How to set dependency to an Oracle Datatable

Question

Hi,

In our project we want to use Cache Dependency.After some searching,i've learned that it is just for Sql Server and we should write our own cachedepency class for it.Has anyone tried it before?What is the easiest way to set dependency to an Oracle table?We use Oracle 11g and .net 2008,Thanks for Help

Answer 1

Oracle allows you to implement a notification scheme using the dbms_alert package. If you google dbms_alert, you'll find many pages giving you examples of how to use it, but basically it involves 3 things:

1. You call dbms_alert.raise (with a label of your choosing) when you want clients to be notified of a change (for exammple, in the insert/update/delete triggers for a table which you need Cache Dependency).

2. The client will hold onto a connection where they have called the dbms_alert.register function (with the particular label being registered).

3. At any time, the client can call dbms_alert.wait or .waitany; the function will block (or you can supply a timeout) until the alert has been raised. In your case, you could call waitany with a short timeout immediately before you need to display the data you were monitoring.

The wait functions work well - you don't need to constantly poll, unless you need to know the second something has changed. Instead, you can just check them when you're about to use your cached data to make sure its up to date. Also, the clients aren't notified until the transaction that raised them commits.

The biggest limitation with dbms_alert is that only one transaction can raise an uncommitted alert of a particular label at one time. In other words, if transaction A raises alert "foo", transaction B will block when it raises "foo" until A rolls back or commits, so it's generally not a good idea to use alerts in transactions that could take a long time (unless blocking other users is part of the design). Also, as mentioned above, the client needs to hold onto a connection that will be used to check for notifications. This can be the same connection you're using to perform any other database activity, but if you're writing an application that does a lot of connection pooling/sharing, you'll need to have a facility for holding onto the connections for the length of time you need to keep the cache coherent.

Answer 2

You can use notification:

http://www.oracle.com/technology/obe/hol08/dotnet/changenotification/odpnetchg_otn.htm