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.

Advertisements

Posted on May 9, 2011, in oracle-jdbc and tagged , . Bookmark the permalink. 1 Comment.

  1. Truly helpful!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: