Oracle Dependency is provided by NCache for synchronizing cache with Oracle database. Item expires if result of the command (based on command text) changes. Oracle Dependency is only available for Oracle database 10g release 2 or later. Also make sure that Oracle Data Providers for .NET (version 10.1.0.2.0 or later) is installed.
i. Configuring Notification on Oracle server
NCache tracks changes in database using notifications received from Oracle server. For this purpose NCache registers Change Notification with oracle server. NCache server should have Change Notification privileges to create notification registration. Grants the Change Notification privilege to the user by following statement.
[SQL]
"grant change notification to scott"
ii. Adding Data with Oracle Dependency
Following code shows how to add data with Oracle Dependency. Altering data after addition with Oracle Dependency will remove it from cache.
//Create object to be added with dependency
Product product=new Product();
product.ProductID=1001;
product.ProductName="Chai";
string key="Product:"+product.ProductID;
try
{
//Creating OracleCacheDependency
String connectionString = "User Id=scott;Password=tiger;Data Source=oracle";
string query = "SELECT rowID, productID FROM dbo.Products WHERE productID = 1001";
OracleCacheDependency oracleDependency = new OracleCacheDependency(connectionString, query);
//Adding cache item "Product:1001" with SqlDependency
cache.Insert(key, product, oracleDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);
//Modifying Product record in database will automatically remove Product from cache
}
catch (OperationFailedException e)
{
// handle exception
}
In Oracle 10g, database change notifications are only object based. This means that change notifications will be fired if any row is modified in object. Therefore rowID needs to be checked to confirm if the row changed is the one for which the event is registered. RowID's cannot be retrieved unless explicitly included in query. Therefore customer has to include rowID in query that they are registering with OracleDependecy, otherwise change notification will be fired if any row is modified in table.
When rowID is included in query like: "select rowID, productID, productname, unitprice from Products where ProductID = 220"; NCache will save the rowID of rows for which change notification is registered. When it receives any change notification, NCache will compare the rowIDs to determine whether the row changed is the one for which the rowID is registered. Otherwise NCache will have no way to check this and items for which change notification is registered will be removed after any row in table changes.
In Oracle 11g both object based and query based (default) notifications are provided. In query based notifications; change of the modified row will be notified only if the change notification is registered for it.
See Also