Oracle Query Notification revisited

A few month ago I compared the .NET query notification possibilites between SQL Server and Oracle. At that time Oracle could only dectect changes at table level; not at row level.

With the new release of ODP.NET 11.1 and Oracle 11g Oracle enhances the Query Notification support. Change detection can be Query-based or Object-based (as with the prior version).

ODP.NET uses a best effort mode, which means that Query-based notification is tried first and requires a 11.1 database with enabled automatic undo management (default). The main restriction is the SELECT list, however. Only VARCHAR2 and NUMBER columns may be selected.

Example of Query-based notification:

using (OracleConnection connection = new OracleConnection("Data Source=DB1; User ID=scott; Password=tiger"))
{
OracleCommand command = new OracleCommand(
"SELECT empno, ename, sal, comm " +
" FROM EMP " +
" WHERE ename = 'SCOTT'", connection);

OracleDependency dependency = new OracleDependency(command);
dependency.RowidInfo = OracleRowidInfo.Include;
command.Notification.IsNotifiedOnce = false;
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

Query Notification will only be executed if one of the selected columns for SCOTT changes. Changing the column DEPTNO for SCOTT or the column SAL for KING will not fire the change event.

Example of Object-based notification:

using (OracleConnection connection = new OracleConnection("Data Source=DB1; User ID=scott; Password=tiger"))
{
OracleCommand command = new OracleCommand(
"SELECT empno, ename, sal, hiredate" +
" FROM EMP " +
" WHERE ename = 'SCOTT'", connection);

OracleDependency dependency = new OracleDependency(command);
dependency.RowidInfo = OracleRowidInfo.Include;
command.Notification.IsNotifiedOnce = false;
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);


Note, that the only difference is the presence of the HIREDATE column which prevents Query-based notification.
Query notifcation will be executed if any column in any row in the EMP table changes.

Of course, other changes such as DDL commands on the target table may also fire the events as it did in the previous versions.

Lesen Sie den vollständigen Eintrag unter http://ursmeier.blogspot.com/2008/04/oracle-query-notification-revisited.html