26 Database Change Notification

Generally, a middle-tier data cache duplicates some data from the back-end database server. Its goal is to avoid redundant queries to the database. However, this is efficient only when the data rarely changes in the database. The data cache has to be updated or invalidated when the data changes in the database. Starting from 11g Release 1 (11.1), Oracle JDBC drivers provide support for the Database Change Notification feature of Oracle Database. Using this functionality of the JDBC drivers, multitier systems can take advantage of the Database Change Notification feature to maintain a data cache as up-to-date as possible, by receiving invalidation events from the JDBC drivers.

The JDBC drivers can register SQL queries with the database and receive notifications in response to the following:

DML or DDL changes on the objects associated with the queries

DML or DDL changes that affect the result set

The notifications are published when the DML or DDL transaction commits (changes made in a local transaction do not generate any event until they are committed).

To use Oracle JDBC driver support for Database Change Notification, perform the following:

Registration: You first need to create a registration.

Query association: After you have created a registration, you can associate SQL queries with it. These queries are part of the registration.

Notification: Notifications are created in response to changes in tables or result set. Oracle database communicates these notifications to the JDBC drivers through a dedicated network connection and JDBC drivers convert these notifications to Java events.

Also, you need to grant the CHANGE NOTIFICATION privilege to the user. For example, if you connect to the database using the SCOTT user name, then you need to run the following command in the database:

Creating a Registration

Creating a registration is a one-time process and is done outside of the currently used transaction. The API for creating a registration in the server is executed in its own transaction and is committed immediately. You need a JDBC connection to create a registration, however, the registration is not attached to the connection. You can close the connection after creating a registration, and the registration survives. In an Oracle RAC environment, a registration is a persistent entity that exists on all nodes. If a node goes down, then the registration continues to exist and will be notified when the tables change.

There are two ways to create a registration:

The JDBC-style of registration: Use the JDBC driver to create a registration on the server. The JDBC driver launches a new thread that listens to notifications from the server (through a dedicated channel) and converts these notification messages into Java events. The driver then notifies all the listeners registered with this registration.

The PL/SQL-style of registration: If you want a PL/SQL stored procedure to handle the notifications, then create a PL/SQL-style registration. As in the JDBC-style of registration, the JDBC drivers enable you to attach statements (queries) to this registration. However the JDBC drivers do not get notifications from the server because the notifications are handled by the PL/SQL stored procedure.

Note:

There is no way to remove one particular object (table) from an existing registration. A workaround would be to either create a new registration without this object or ignore the events that are related to this object.

You can use the registerDatabaseChangeNotification method of the oracle.jdbc.OracleConnection interface to create a JDBC-style of registration. You can set certain registration options through the options parameter of this method. Table 26-1 lists some of the registration options that can be set. To set these options, use the java.util.Properties object. These options are defined in the oracle.jdbc.OracleConnection interface. The registration options have a direct impact on the notification events that the JDBC drivers will create. Example 26-1 illustrates how to use the Database Change Notification feature.

The registerDatabaseChangeNotification method creates a new database change registration in the database server with the given options. It returns a DatabaseChangeRegistration object, which can then be used to associate a statement with this registration. It also opens a listener socket that will be used by the database to send notifications.

Note:

If a listener socket (created by a different registration) exists, then this socket will be used by the new database change registration as well.

Table 26-1 Database Change Notification Registration Options

Option

Description

DCN_IGNORE_DELETEOP

If set to true, DELETE operations will not generate any database change event.

DCN_IGNORE_INSERTOP

If set to true, INSERT operations will not generate any database change event.

DCN_IGNORE_UPDATEOP

If set to true, UPDATE operations will not generate any database change event.

DCN_NOTIFY_CHANGELAG

Specifies the number of transactions by which the client is willing to lag behind.

Note: If this option is set to any value other than 0, then ROWID level granularity of information will not be available in the events, even if the DCN_NOTIFY_ROWIDS option is set to true.

DCN_NOTIFY_ROWIDS

Database change events will include row-level details, such as operation type and ROWID.

Note: This option is available only when running against an 11.0 database.

NTF_LOCAL_HOST

Specifies the IP address of the computer that will receive the notifications from the server.

NTF_LOCAL_TCP_PORT

Specifies the TCP port that the driver should use for the listener socket.

NTF_QOS_PURGE_ON_NTFN

Specifies if the registration should be expunged on the first notification event.

NTF_QOS_RELIABLE

Specifies whether or not to make the notifications persistent, which comes at a performance cost.

NTF_TIMEOUT

Specifies the time in seconds after which the registration will be automatically expunged by the database.

If there exists a registration, then you can also use the getDatabaseChangeRegistration method to map the existing registration with a new DatabaseChangeRegistration object. This method is particularly useful if you have created a registration using PL/SQL and want to associate a statement with it.

See:

Refer to the Javadoc for more information about the APIs.

Associating a Query with a Registration

After you have created a registration or mapped to an existing registration, you can associate a query with it. Like creating a registration, associating a query with a registration is a one-time process and is done outside of the currently used registration. The query will be associated even if the local transaction is rolled back.

You can associate a query with registration using the setDatabaseChangeRegistration method defined in the OracleStatement class. This method takes a DatabaseChangeRegistration object as parameter. The following code snippet illustrates how to associate a query with a registration:

...
// conn is a OracleConnection object.
// prop is a Properties object containing the registration options.
DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotifictaion(prop);
...
Statement stmt = conn.createStatement();
// associating the query with the registration
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
// any query that will be executed with the 'stmt' object will be associated with
// the registration 'dcr' until 'stmt' is closed or
// '((OracleStatement)stmt).setDatabaseChangeRegistration(null);' is executed.
...

Notifying Database Change Events

To receive database change notifications, attach a listener to the registration. When a database change event occurs, the database server notifies the JDBC driver. The driver then constructs a new Java event, identifies the registration to be notified, and notifies the listeners attached to the registration. The event contains the object ID of the database object that has changed and the type of operation that caused the change. Depending on the registration options, the event may also contain row-level detail information. The listener code can then use the event to make decisions about the data cache.

Note:

The listener code must not slow down the JDBC notification mechanism. If the code is time-consuming, for example, if it refreshes the data cache by querying the database, then it needs to be executed within its own thread.

You can attach a listener to a registration using the addListener method. The following code snippet illustrates how to attach a listener to a registration:

Deleting a Registration

You need to explicitly unregister a registration to delete it from the server and release the resources in the driver. You can unregister a registration using a connection different from one that was used for creating it. To unregister a registration, you can use the unregisterDatabaseChangeNotification method defined in oracle.jdbc.OracleConnection.

You must pass the DatabaseChangeRegistration object as a parameter to this method. This method deletes the registration from the server and the driver and closes the listener socket.

If the registration was created outside of JDBC, say using PL/SQL, then you must pass the registration ID instead of the DatabaseChangeRegistration object. The method will delete the registration from the server, however, it does not free any resources in the driver.

Example

Example 26-1 illustrates how to use the Database Change Notification feature. In this example, the SCOTT user is connecting to the database. Therefore in the database you need to grant the following privilege to the user:

This code will also work with Oracle Database 10g Release 2 (10.2). This code uses table registration. That is, when you register a SELECT query, what you register is the name of the tables involved and not the query itself. In other words, you might select one single row of a table and if another row is updated, you will be notified although the result of your query has not changed.

In this example, if you leave the registration open instead of closing it, then the database change notification thread continues to run. Now if you run a DML query that changes the SCOTT.DEPT table and commit it, say from SQL*Plus, then the Java program prints the notification.