Sync Cache with Oracle

NCache provides OracleCacheDependency for notification based dependencies with Oracle. Internally, NCache uses OracleDependency to register data change notifications with the Oracle database server. Hence you need to understand the limitations and working mechanisms of OracleDependency while using this dependency. For example, limited sets of queries can be registered with OracleDependency. See the following Oracle Documentation for more detail.

• Special Considerations Using Query Notifications

• OracleDependency

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.

Note

Before using Oracle Dependency, set up Oracle Database Environment by referring to Setup Oracle Database Environment section in Administrator's Guide.

Pre-Requisites for Using Oracle Dependency

• Set up database environment before using Oracle Dependency.
• Include the following namespace in your application:
  • Alachisoft.NCache.Web.Caching
  • Alachisoft.NCache.Runtime
  • Alachisoft.NCache.Runtime.Dependencies
• Cache must be running.
• Make sure that the data being added is serializable.
• To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Adding Data with Oracle Dependency

Altering data after addition with Oracle Dependency will remove it from the cache. OracleCacheDependency is used to specify the dependency criteria and then item is added in the cache using the Add/Insert method.

The following example adds data with OracleCacheDependency using the Insert method. The Insert method adds a new item with dependency and if the item already exists in the cache it overwrites its properties.

Note

Note that rowId needs to be mentioned in the query for Oracle 10g. However it is not required for the latest Oracle Version.

try
{
      // Creating a connection string to establish connection with the database
      // Connection String is in <AppSettings>
      string connectionString = ConfigurationManager.AppSettings["connectionstring"];

      // Create the query which selects the data on which key is dependent
      // Note that rowID needs to be mentioned in the query for Oracle Version 10g
      string query = "SELECT productName FROM Products WHERE productID = 1001";

      // Get Product from database against given product ID
      Product product = FetchProductFromDB(connectionString, query);

      // Generate a unique cache key for this product
      string key = $"Product:{product.ProductID}";

      // Create Oracle Dependency
      OracleCacheDependency oracleDependency = new OracleCacheDependency(connectionString, query);

      // Create a new cacheitem and add oracle dependency to it
      CacheItem item = new CacheItem(product);
      item.Dependency = oracleDependency;

      //Add cache item in the cache with Oracle Dependency
      cache.Insert(key, item);

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present
      // This can be done by using
      // cache.Contains()
      // cache.Count
}
catch (OperationFailedException ex)
{
      if (ex.Message.Contains("Name-Value premature end of string"))
      {
            // Make sure that the connection string is valid
      }
      else if (ex.Message.Contains("keyword not found where expected"))
      {
            // Make sure that the query is valid
      }
      else
      {
            // Exception can occur due to:
            // Connection Failures
            // Operation performed during state transfer
            // Operation Timeout
      }
}
catch (Exception ex)
{
      // Any generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Add Data with Oracle Dependency using Stored Procedure

NCache provides you with an ability to provide Oracle Dependency on an item using stored procedure. You can also specify the parameters to be passed along with the stored procedure using the OracleCacheDependency method.

The following example adds item to the cache with Oracle Dependency through stored procedure using the Insert method. The Insert method adds a new item with dependency and if the item already exists in the cache it overwrites its properties.

try
{
      // Get product from database against given product ID
      Product product = FetchProductFromDB(1001);

      // Generate a unique cache key for this product
      string key = $"Product:{product.ProductID}";

      // Create a connection string to establish connection with the database
      // Connection String is in <AppSettings> in App.config
      string connectionString = ConfigurationManager.AppSettings["connectionstring"];

      // The name of the stored procedure the item is dependent on
      string storedProcName = "Award_Bonus";

      // Create Oracle Dependency
      // Null is passed as CmdParams
      OracleCacheDependency oracleDependency = new OracleCacheDependency(connectionString, storedProcName, OracleCommandType.StoredProcedure, null);

      // Create a new cacheitem and add oracle dependency to it
      CacheItem item = new CacheItem(product);
      item.Dependency = oracleDependency;

      //Add cacheitem in the cache with Oracle Dependency
      cache.Insert(key, item);

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present
      // This can be done by using
      // cache.Contains()
      // cache.Count
}
catch (OperationFailedException ex)
{
      if (ex.Message.Contains("Name-Value premature end of string"))
      {
            // Make sure that the connection string is valid
      }
      else
      {
            // Exception can occur due to:
            // Connection Failures
            // Operation performed during state transfer
            // Operation Timeout
      }
}
catch (Exception ex)
{
      // Any generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Special Consideration for Oracle Version

Oracle 10g

In Oracle 10g, database change notifications are only object based. This means that change notifications will be fired if any row is modified in an object. Therefore, it is recommended to check rowID to confirm if the altered row is the one for which the event was registered. RowIDs cannot be retrieved unless explicitly included in query. So the user has to specifically include rowID in the query that is being registered with OracleDependency, otherwise the change notification will be fired if any row is modified in table.

When rowID is included in a query such as – Select rowID, productID, productname, unitprice from Products where ProductID = 220 - NCache will save the rowIDs of rows for which the 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 against this and items for which change notification is registered may be removed if any row in the table changes.

Oracle 11g

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.

Additional Resources

NCache provides sample application for Oracle dependency at:

• GitHub

• Shipped with NCache: %NCHOME%\samples\dotnet\Dependencies\OracleDependency

See Also

Sync Cahce with SQL Server
Sync Cache with OleDb
Using CLR Procedures in SQL Server
Using Locks for Concurrent Updates
Sync Cache with External Source