Cache Data Dependency on Oracle Database [Deprecated]
NCache provides OracleCacheDependency 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 further details.
NCache provides an Oracle Dependency to synchronize the cache with the Oracle database. Cache items expire if a command results in a change in the database. Oracle Dependency is available for Oracle database 11g or later. Also, install Oracle Data Providers for .NET (version 19.5.0+).
The database change notifications are object-based, firing change notifications upon any object row modification. Therefore, 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 the 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 the table.
When ROWID is included in a query such as: SELECT ROWID, UnitPrice FROM Products WHERE ProductID = {product.ProductID} 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 with a change notification registered are removed if any row in the table changes.
Prerequisites
Before using the NCache client-side APIs, ensure that the following prerequisites are fulfilled:
- Add the following Maven dependencies for your Java client application in
pom.xml file:
<dependency>
<groupId>com.alachisoft.ncache</groupId>
<!--for NCache Enterprise-->
<artifactId>ncache-client</artifactId>
<version>x.x.x</version>
</dependency>
- Install either of the following NuGet packages in your .NET client application:
- Enterprise:
Install-Package Alachisoft.NCache.SDK -Version 4.9.1.0
- Create a new Console Application.
- Make sure that the data being added is serializable.
- Add NCache References by locating
%NCHOME%\NCache\bin\assembly\4.0 and adding Alachisoft.NCache.Web and Alachisoft.NCache.Runtime as appropriate.
- Include the
Alachisoft.NCache.Runtime.Dependencies namespace in your application.
- To learn more about the NCache Legacy API, please download the NCache 4.9 documents available as a .zip file on the Alachisoft Website.
Adding Data
Altering data after addition with Oracle Dependency will remove it from the cache. The OracleCacheDependency is used to specify the dependency criteria, and then the item is added to the cache using the Add or Insert method. The following example adds data with OracleCacheDependency using the Add method.
try
{
// Precondition: Cache is already connected
// Creating connection string to get connected with the database
string connectionString = "your_connection_string_here";
// Getting products from the database
List<Product> products = FetchProductFromDB();
foreach (Product product in products)
{
string productKey = $"Product: {product.ProductID}";
// Creating an Oracle dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache
string query = $"SELECT ROWID, UnitPrice FROM Products WHERE ProductID = {product.ProductID}";
OracleCacheDependency dependency = new OracleCacheDependency(connectionString, query);
CacheItem productItem = new CacheItem(product);
// Adding Dependency to product item
productItem.Dependency = dependency;
// Adding CacheItem in cache
cache.Add(productKey, productItem);
}
}
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
{
// Precondition: Cache is already connected
// Create a connection string to get connected with the database
String connectionString = "your_connection_string_here";
// Getting products from the database
List<Product> products = fetchProductFromDB();
for (Product product : products)
{
String productKey = "Product:" + product.getProductID();
// Creating an Oracle dependency on the UnitPrice of the product.
// Whenever the UnitPrice changes, the product is removed from the cache
String query = "SELECT ROWID, UnitPrice FROM Products WHERE ProductID = " + product.getProductID();
OracleCacheDependency dependency = new OracleCacheDependency(connectionString, query);
CacheItem productItem = new CacheItem(product);
// Adding Dependency to product item
productItem.setDependency(dependency);
// Adding CacheItem in cache
cache.insert(productKey, productItem);
}
}
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 connection string to get connected with the database
conn_string = "your_connection_string_here"
# Getting products from the database
products = fetch_products_from_db()
for product in products:
product_key = f"Product:{product.get_product_id()}"
# Creating an Oracle dependency on the UnitPrice of product.
# Whenever the UnitPrice changes, the product is removed from the cache
query = f"SELECT ROWID, UnitPrice FROM Products WHERE ProductID = {product.get_product_id()}"
oracle_cache_dependency = OracleCacheDependency(conn_string, query)
cache_item = CacheItem(product)
# Adding dependency to product item
cache_item.set_dependency(oracle_cache_dependency)
# Adding CacheItem in cache
cache.insert(product_key, cache_item)
except Exception as error:
# Handle any errors
try
{
// Precondition: Cache is already connected
// Creating connection string to get connected with the database
const connectionString = "your_connection_string_here";
// Getting products from the database
const products = fetchProductsFromDB();
for (const product of products)
{
const productKey = `Product:${product.ProductID}`;
// Creating an Oracle dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache
const query = `SELECT ROWID, UnitPrice FROM Products WHERE ProductID = ${product.ProductID}`;
const dependency = new ncache.OracleCacheDependency(connectionString, query);
const cacheItem = new ncache.CacheItem(JSON.stringify(product));
// Adding Dependency to product item
cacheItem.setDependency(dependency);
// Adding CacheItem in cache
await cache.insert(productKey, cacheItem);
}
}
catch (error)
{
// Handle any errors
}
// Using NCache Enterprise 4.9.1
try
{
// Precondition: Cache is already connected
// Creating connection string to get connected with the database
string connectionString = "your_connection_string_here";
// Getting products from the database
List<Product> products = FetchProductFromDB();
foreach (Product product in products)
{
string productKey = $"Product: {product.ProductID}";
// Creating an Oracle dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache.
string query = $"SELECT ROWID, UnitPrice FROM Products WHERE ProductID = {product.ProductID}";
OracleCacheDependency oracleDependency = new OracleCacheDependency(connectionString, query);
// Adding product with SqlDependency
cache.Insert(productKey, product, oracleDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);
}
}
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
}
Note
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 lets you add data with an Oracle Dependency using a stored procedure. You can also specify the parameters to be passed along with the stored procedure, using the OracleCacheDependency method. The following example adds items to the cache with Oracle Dependency through the stored procedure using the Add method.
// Precondition: Cache is already connected
// Creating connection string to get connected with the database
string connectionString = "your_connection_string_here";
string spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";
// Getting products from the database
List<Product> products = FetchProductFromDB();
foreach (Product product in products)
{
string productKey = $"Product: {product.ProductID}";
// Creating Param to be passed in stored procedure dictionary
OracleCmdParams paramProductID = new OracleCmdParams
{
Type = OracleCmdParamsType.Int32,
Value = product.ProductID
};
// Creating stored procedure params
Dictionary<string, OracleCmdParams> parameters = new Dictionary<string, OracleCmdParams>();
parameters.Add("@ProductID", paramProductID);
CacheItem productItem = new CacheItem(product);
// Creating an Oracle dependency on the result of the stored procedure,
// which returns UnitPrice for the given ProductID.
// If the UnitPrice (or the row's ROWID) changes, the cache item is removed.
OracleCacheDependency dependency = new OracleCacheDependency(connectionString, spGetUnitPriceByProductID, OracleCommandType.StoredProcedure, parameters);
// Adding Dependency to product item
productItem.Dependency = dependency;
// Adding CacheItem in cache
cache.Add(productKey, productItem);
}
// Precondition: Cache is already connected
// Create a connection string to get connected with the database
String connectionString = "your_connection_string_here";
String spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";
// Getting products from the database
List<Product> products = fetchProductFromDB();
for (Product product : products)
{
String productKey = "Product:" + product.getProductID();
// Creating Param to be passed in the stored procedure dictionary
OracleCmdParams paramProductID = new OracleCmdParams();
paramProductID.setType(OracleCmdParamsType.Int32);
paramProductID.setValue(product.getProductID());
// Creating stored procedure params using the constructor
Map<String, OracleCmdParams> parameters = new HashMap<>();
parameters.put("@ProductID", paramProductID);
CacheItem productItem = new CacheItem(product);
// Creating an Oracle dependency on the result of the stored procedure,
// which returns UnitPrice for the given ProductID.
// If the UnitPrice (or the row's ROWID) changes, the cache item is removed.
OracleCacheDependency dependency = new OracleCacheDependency(connectionString, spGetUnitPriceByProductID, OracleCommandType.StoredProcedure, parameters);
// Adding Dependency to the product item
productItem.setDependency(dependency);
// Adding CacheItem to the cache
cache.insert(productKey, productItem);
}
# Precondition: Cache is already connected
# 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
// Precondition: Cache is already connected
// This is an async method
// Creating connection string to get connected with the database
const connectionString = "your_connection_string_here";
const spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";
// Getting products from the database
const products = fetchProductsFromDB();
for (const product of products)
{
const productKey = `Product:${product.ProductID}`;
// Creating Param to be passed in the stored procedure dictionary
const paramProductID = new ncache.OracleCmdParams();
paramProductID.setType(ncache.OracleCmdParamsType.Int32);
paramProductID.setValue(product.ProductID);
// Creating stored procedure params map (dictionary)
const parameters = new Map();
parameters.set("@ProductID", paramProductID);
// Creating CacheItem with product object
const productItem = new ncache.CacheItem(JSON.stringify(product));
// Creating an Oracle dependency on the result of the stored procedure,
// which returns UnitPrice for the given ProductID.
// If the UnitPrice (or the row's ROWID) changes, the cache item is removed.
const dependency = new ncache.OracleCacheDependency(connectionString, spGetUnitPriceByProductID, OracleCommandType.StoredProcedure, parameters);
// Adding dependency to the cache item
productItem.setDependency(dependency);
// Adding cache item in cache
await cache.insert(productKey, productItem);
}
Additional Resources
NCache provides a sample application for Oracle dependency on GitHub.
See Also
.NET: Alachisoft.NCache.Runtime.Dependencies namespace.
Java: com.alachisoft.ncache.runtime.dependencies namespace.
Python: ncache.runtime.dependencies class.
Node.js: OracleCacheDependency class.
