Cache Data Dependency on Oracle Database

Note

This feature is only available in NCache Enterprise Edition.

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. See the following Oracle Documentation for more detail.

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 available for Oracle database 11g or later. Also make sure that Oracle Data Providers for .NET (version 10.1.0.2.0 or later) is installed.

The database change notifications are 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.

Note

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

Prerequisites

Set up database environment before using Oracle Dependency.
Install the following NuGet package in your application:
- Enterprise: Alachisoft.NCache.SDK
Include the following namespace in your application:
- Alachisoft.NCache.Client
- Alachisoft.NCache.Runtime.Dependencies
- Alachisoft.NCache.Runtime.Exceptions
Cache must be running.
Make sure that the data being added is serializable.
For API details, refer to: ICache, CacheItem, Dependency, OracleCacheDependency.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.

Add the following Maven dependencies in your pom.xml file:

<dependency>
    <groupId>com.alachisoft.ncache</groupId>
    <artifactId>ncache-client</artifactId>
    <version>x.x.x</version>
</dependency>

Import the following packages in your application:
- import com.alachisoft.ncache.client.*;
- import com.alachisoft.ncache.runtime.exceptions.*;
Cache must be running.
The application must be connected to cache before performing the operation.
Make sure that the data being added is serializable.
For API details, refer to:Cache,CacheItem, setDependency, OracleCacheDependency.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.

Install and include the following module in your application:
- Enterprise: const ncache = require('ncache-client')
Cache must be running.
The application must be connected to cache before performing the operation.
For API details, refer to: Cache, CacheItem, setDependency, OracleCacheDependency.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.

Install the NCache Python client by executing the following command:

# Enterprise Client
pip install ncache-client

Import the NCache module in your application.
Cache must be running.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.

Add the following Maven dependencies in your pom.xml file:

<dependency>
    <groupId>com.alachisoft.ncache</groupId>
    <artifactId>ncache-scala-client</artifactId>
    <version>x.x.x</version>
</dependency>

Import the following packages in your application:
- import com.alachisoft.ncache.scala.client.*;
- import com.alachisoft.ncache.scala.runtime.dependencies.*;
Cache must be running.
The application must be connected to cache before performing the operation.
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.
To handle any unseen exceptions, refer to the Troubleshooting section.

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.

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
      string query = "SELECT ROWID, ProductID, ProductName, UnitPrice FROM Products WHERE ProductID > :productID";

      var param = new OracleCmdParams();
      param.Type = (OracleCmdParamsType.Int16);
      param.Value = 1020;
      param.Direction = OracleParameterDirection.Input;

      // Adding the populated parameter to a dictionary
      Dictionary<string, OracleCmdParams> oracleParam = new Dictionary<string, OracleCmdParams>();
      oracleParam.Add("productID", param);

      // Create Oracle Dependency
      var oracleDependency = new OracleCacheDependency(connString, query, OracleCommandType.Text, oracleParam);

      // Get Product from database against given product ID
      Product product = FetchProductFromDB(param.Value);

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

      // Create a CacheItem and add Oracle dependency to it
      var cacheItem = new CacheItem(product);
      cacheItem.Dependency = oracleDependency;

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

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present
}
catch (OperationFailedException ex)
{
      // Exception can occur due to:
      // Connection Failures
      // Operation Timeout
      // Operation performed during state transfer
}
catch (Exception ex)
{
      // Any generic exception like ArgumentNullException or ArgumentException
      // Exception may occur due to incorrect query format or invalid connection string
}

try 
{
      // Creating a connection string to establish connection with the database
      String connString = "";
      Connection connection = DriverManager.getConnection(connString);

      // Create query which selects data on which cache data is dependent
      String query = "SELECT rowID, productName FROM Products WHERE productID = ?";
      QueryCommand queryCommand = new QueryCommand(query);
      queryCommand.getParameters().put("ProductID", productId);

      // Get product from database against productId
      Product product = fetchProductFromDb(productID);

      // Create unique cache key for this product
      String key = "Product:" + product.productID;

      // Create oracle dependency 
      OracleCacheDependency oracleCacheDependency = new OracleCacheDependency(String.valueof(connection), query);

      // Create new cache item and add oracle dependency to it
      CacheItem cacheItem = new CacheItem(product);
      cacheItem.setDependency(oracleCacheDependency);

      // Add cache item in the cache item with oracle dependency 
      cache.insert(key, cacheItem);

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present
}
catch (OperationFailedException ex) 
{
      // Exception can occur due to:
      // Connection Failures
      // Operation Timeout
      // Operation performed during state transfer
}
catch (Exception ex) 
{
    // Any generic exception like IllegalArgumentException or NullPointerException
}

try 
{
      // Creating a connection string to establish connection with the database
      var connection = new ActiveXObject("adodb.connection");
      let connString = "...";
      connection.Open(connString);

      var result = new ActiveXObject("ADODB.Recordset");

      let query = "Select RowID, ProductId, ProductName, UnitPrice FROM dbo.Products WHERE ProductId = ?";
      let queryCommand = new QueryCommand(query);
      queryCommand.getParameters().put("CustomerID", this.customerId);
      result.Open(query, connection);

      // Get product from database against productId
      let product = this.fetchProductFromDb(this.productId);

      // Create unique cache key for this product
      let key = "Product:" + product.ProductID;

      // Create oracle dependency 
      var oracleCacheDependency = new ncache.OracleCacheDependency(connString, query);

      // Create new cache item and add oracle dependency to it
      let cacheItem = new ncache.CacheItem(product);
      cacheItem.setDependency(oracleCacheDependency);

      // Add cache item in the cache item with oracle dependency 
      this.cache.insert(key, cacheItem);

      result.close();
      connection.close();

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present
}
catch (error) 
{
   // Handle any errors
}

try:
      # Creating a connection string to establish connection with the database
      conn_string = "your_connection_string"

      query = "Select RowID, ProductId, ProductName, UnitPrice FROM dbo.Products WHERE ProductId = ?"
      query_command = ncache.QueryCommand(query)
      query_command.set_parameters({"CustomerID": "ALFKI"})

      # Get product from database against productId
      product = fetch_product_from_db("1001")

      # Create unique cache key for this product
      key = "Product:" + product.get_product_id()

      # Create oracle dependency
      oracle_cache_dependency = ncache.OracleCacheDependency(conn_string, query)

      # Create new cache item and add oracle dependency to it
      cache_item = ncache.CacheItem(product)
      cache_item.set_dependency(oracle_cache_dependency)

      # Add cache item in the cache item with oracle dependency
      cache.insert(key, cache_item)

      # For successful addition of item with Oracle Dependency
      # Update the record in the database and check if key is present
except Exception as exp:
      # Handle errors

try {
    // Creating a connection string to establish connection with the database

    val connString = "YourConnString"
    val productID = "1001"

    // Create query which selects data on which cache data is dependent
    val query = "SELECT rowID, productName FROM Products WHERE productID = ?"
    val queryCommand = new QueryCommand(query)
    queryCommand.setParameters(Map("ProductID" -> productID))

    // Get product from database against productId
    val product = fetchProductFromDb(productID)

    // Create unique cache key for this product
    val key = "Product:" + product.getProductId

    // Create oracle dependency
    val oracleCacheDependency = OracleCacheDependency(connString, query)

    // Create new cache item and add oracle dependency to it
    val cacheItem = new CacheItem(product)
    cacheItem.setDependency(oracleCacheDependency)

    // Add cache item in the cache item with oracle dependency
    cache.insert(key, cacheItem)

    // For successful addition of item with Oracle Dependency
    // Update the record in the database and check if key is present
}
catch {
    case exception: Exception => {
      // Handle any errors
    }
}

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
{
      // 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 = "GetProductByID";

      OracleCmdParams param = new OracleCmdParams();
      param.Type = (OracleCmdParamsType.Int16);
      param.Value = 1020;
      param.Direction = OracleParameterDirection.Input;

      var oracleCmdParams = new Dictionary<string, OracleCmdParams>();
      oracleCmdParams.Add("productID", param);

      // Create Oracle Dependency
      var oracleDependency = new OracleCacheDependency(connString, storedProcName, OracleCommandType.StoredProcedure, oracleCmdParams);

      // Get product from database against given product ID
      Product product = FetchProductFromDB(param.Value);

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

      // Create a new cacheitem and add Oracle dependency to it
      var cacheItem = new CacheItem(product);
      cacheItem.Dependency = oracleDependency;

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

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present
}
catch (OperationFailedException ex)
{
    // Exception can occur due to:
    // Connection Failures
    // Operation Timeout
    // Operation performed during state transfer
}
catch (Exception ex)
{
      // Any generic exception like ArgumentNullException or ArgumentException
      // Exception may occur due to incorrect query format or invalid connection string
}

try 
{
      // Create connection with the database
      String connString = "your_connection_string_here";
      Connection connection = DriverManager.getConnection(connString);

      // Name of the stored procedure the item is dependent on
      String storedProcedureName = "GetProductByID";

      // Specify the Product ID passed as parameters
      Map<String, OracleCmdParams> oracleCmdParamsMap = new HashMap<>();
      oracleCmdParamsMap.put("ProductID", productId);

      // Create oracle dependency with stored procedure 
      OracleCacheDependency oracleCacheDependency = new OracleCacheDependency(connString, storedProcedureName,
                  OracleCommandType.StoredProcedure, oracleCmdParamsMap);

      // Get product from database against productId
      Product product = fetchProductFromDb(productId);

      // Generate a unique cache key for this product
      String key = "Product:" + product.productId;

      // Create a new cache item and add oracle dependency to it
      CacheItem cacheItem = new CacheItem(product);
      cacheItem.setDependency(oracleCacheDependency);

      // Add cache item in the cache with oracle dependency 
      cache.insert(key, cacheItem);

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present
} 
catch (OperationFailedException ex) 
{
      // Exception can occur due to:
      // Connection Failures
      // Operation Timeout
      // Operation performed during state transfer
} 
catch (Exception ex) 
{
   // Any generic exception like IllegalArgumentException or NullPointerException
}

// This is an async method
try 
{
      // Create connection with the database
      var connection = new ActiveXObject("adodb.connection");
      let connString = "your_connection_string_here";
      connection.Open(connString);

      // Name of the stored procedure the item is dependent on
      let storedProcedureName = "GetProductByID";

      // Specify the Product ID passed as parameters
      let oracleCmdParamsMap = new Map();
      oracleCmdParamsMap.set("ProductID", this.productId);

      // Create oracle dependency with stored procedure 
      var oracleCacheDependency = new ncache.OracleCacheDependency(connString, storedProcedureName,
            OracleCommandType.StoredProcedure, oracleCmdParamsMap);

      // Get product from database against productId
      let product = this.fetchProductFromDb(this.productId);

      // Generate a unique cache key for this product
      let key = "Product:" + product.ProductID;

      // Create a new cache item and add oracle dependency to it
      let cacheItem = new ncache.CacheItem(product);
      cacheItem.setDependency(oracleCacheDependency);

      // Add cache item in the cache with oracle dependency 
      await this.cache.insert(key, cacheItem);

      // For successful addition of item with Oracle Dependency
      // Update the record in the database and check if key is present

      connection.close();
}
catch (error) 
{
    // Handle any errors 
}

try:
      # Create a connection string to connect with the database
      conn_string = "your_connection_string_here"

      # Name of the stored procedure the item is dependent upon
      stored_procedure_name = "GetProductByID"

      # Specify the Product ID passed as parameters
      oracle_cmd_params = {"ProductID": "ALFKI"}

      # Create oracle dependency with stored procedure
      oracle_cache_dependency = ncache.OracleCacheDependency(conn_string, stored_procedure_name,
                                                            ncache.OracleCommandType.STORED_PROCEDURE,
                                                            oracle_cmd_params)

      # Get product from database against productId
      product = fetch_product_from_db("ALFKI")

      # Generate a unique cache key for this product
      key = "Product:" + product.get_product_id()

      # Create a new cache item and add oracle dependency to it
      cache_item = ncache.CacheItem(product)
      cache_item.set_dependency(oracle_cache_dependency)

      # Add cache item in the cache with oracle dependency
      cache.insert(key, cache_item)

      # For successful addition of item with Oracle Dependency
      # Update the record in the database and check if key is present
      except Exception as exp:
      # Handle errors

try {
    // Create connection with the database

    val connString = "YourConnectionString"
    val productId = "1001"

    // Name of the stored procedure the item is dependent on
    val storedProcedureName = "GetProductByID"

    // Specify the Product ID passed as parameters
    val param = OracleCmdParams()
    param.setValue(productId)

    val oracleCmdParamsMap: Map[String, OracleCmdParams] = Map("ProductID" -> param)

    // Create oracle dependency with stored procedure
    val oracleCacheDependency = OracleCacheDependency(
      connString,
      storedProcedureName,
      OracleCommandType.StoredProcedure,
      oracleCmdParamsMap
    )

    // Get product from database against productId
    val product = fetchProductFromDb(productId)

    // Generate a unique cache key for this product
    val key = "Product:" + product.getProductId

    // Create a new cache item and add oracle dependency to it
    val cacheItem = CacheItem(product)
    cacheItem.setDependency(oracleCacheDependency)

    // Add cache item in the cache with oracle dependency
    cache.insert(key, cacheItem)

    // For successful addition of item with Oracle Dependency
    // Update the record in the database and check if key is present
}
catch {
    case exception: Exception => {
      // Handle any errors
    }
}

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

Additional Resources

NCache provides sample application for Oracle dependency on GitHub.

Cache Data Dependency on Oracle Database

Note

Note

Prerequisites

Adding Data with Oracle Dependency

Add Data with Oracle Dependency using Stored Procedure

Additional Resources

See Also