Oracle Database Change Notification and OCI JDBC Driver

Oracle supports a feature called query change notification which allows an application to register queries with the database for either object change notification (default) or query result change notification. This feature is a good candidate for an application to implement caching.

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:

1. Registration: You first need to create a registration.
2. Query association: After you have created a registration, you can associate SQL queries with it. These queries are part of the registration.
3. 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.

Some good resources to understand this feature can be found from the oracle documentation.

This feature is not supported yet by the OCI JDBC driver (version 11.2.0.1.0). The registration step fails with an exception “java.sql.SQLException: Unsupported feature”. This can also be verified by decompiling the driver and having a look at the oracle.jdbc.driver.PhysicalConnection doRegisterDatabaseChangeNotification() method.
Note that the thin driver supports this feature.

Advertisement