13 Developing Applications with Database Change Notification

This section contains the following topics:

What Is Database Change Notification?

Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.

During registration, the application specifies a notification handler and associates a set of interesting queries with the notification handler. A notification handler can be either a server side PL/SQL procedure or a client side C callback. Registrations are created on all objects referenced during the execution of the queries. The notification handler is invoked when a transaction subsequently changes any of the registered objects and commits.

Let us assume that the application is interested in being notified about result set changes to a query on the HR.EMPLOYEES table. The application can register the query on the hr.employees table with the database using the Change Notification Feature. If a user adds an employee, then the application can receive a database change notification when a new row is added to the table. A new query of hr.employees returns the changed result set.

When the database issues change notification, it can contain some or all of the following information:

  • Names of the modified objects. For example, the notification can specify that the hr.employees table was changed.

  • The type of change. For example, the message specifies whether the change was caused by an INSERT, UPDATE, DELETE, ALTER TABLE, or DROP TABLE.

  • The ROWIDs of the changed rows and the type of DML that changed them.

  • Global events such as STARTUP and SHUTDOWN (consistent only). In a Real Applications Cluster, the database delivers a notification when the first instance on the database starts or the last instance shuts down.

The notification contains only metadata about the changed rows or objects rather than the changed data itself. For example, the database does not notify the client that a monthly salary increased from 5000 to 6000. To obtain more recent values for the changed objects or rows, the client must query the database based on the information contained in the notification.

Database Change Notification is useful for an application that caches query result sets on mostly read-only objects in the mid-tier to avoid network round trips to the database. Such an application can create a registration on the queries it is interested in caching using the change notification service. On changes to objects referenced inside those queries, the database publishes a change notification when the underlying transaction commits. In response to the notification, the application can refresh its cache by re-executing the queries.

For example, the users of a Web forum application may not need to view new content as soon as it is inserted into the back-end database. Such an application is intrinsically tolerant of slightly out-of-date data, and hence can benefit from caching in the mid-tier. Database change notification is of help in this scenario in keeping the cache updated with the back-end database.

Using Database Change Notification in the Middle Tier

Database Change Notification is relevant in many development contexts, but is particularly useful to mid-tier applications that rely on cached data. Figure 13-1 illustrates a typical scenario in which a back-end Oracle Database serves data that is cached in the middle-tier and then accessed over the Internet.

Figure 13-1 Example of Mid-Tier Caching

Description of Figure 13-1 follows
Description of "Figure 13-1 Example of Mid-Tier Caching"

Applications in the middle tier require rapid access to cached copies of database objects while keeping the cache as current as possible in relation to the database. Cached data becomes out of date or "stale" when a transaction modifies the data and commits, thereby putting the application at risk of accessing incorrect results. If the application uses Database Change Notification, then Oracle Database can publish a notification when a change occurs to registered objects with details on what changed. In response to the notification, the application can refresh cached data by fetching it from the back-end database.

Figure 13-2 illustrates the process by which middle-tier Web clients can receive change and process notifications.

Figure 13-2 Basic Process of Database Change Notification

Description of Figure 13-2 follows
Description of "Figure 13-2 Basic Process of Database Change Notification"

The explanation of the steps in Figure 13-2 is as follows:

  1. In this example, let's assume that the application has cached the result set of a query on HR.EMPLOYEES. The developer creates a registration for the query on HR.EMPLOYEES using the Change Notification PL/SQL Interface. In addition, he creates a stored PL/SQL procedure to process notifications and supplies the server-side PL/SQL procedure as the notification handler.

  2. The database populates the registration information in the data dictionary.

  3. A user modifies one of the registered objects with DML statements and commits the transaction. For example, a user updates a row in the hr.employees table on the back-end database. The data for hr.employees cached in the middle tier is now stale.

  4. Oracle Database adds a message that describes the change to an internal queue.

  5. A JOBQ background process is notified of a new change notification message.

  6. The JOBQ process executes the stored procedure specified by the client application. In this example, JOBQ passes the data to a server-side PL/SQL procedure. The implementation of the PL/SQL callback procedure determines how the notification is handled.

  7. Inside the server-side PL/SQL procedure, the developer can implement logic to notify the mid-tier client application of the changes to the registered objects. For example, it notifies the application of the ROWID of the changed row in hr.employees.

  8. The client application in the middle tier queries the back-end database to retrieve the data in the changed row.

  9. The client application updates the cache with the new data.

Note:

The above steps are applicable to registrations created through PL/SQL. In the case of registrations created via the OCI interface, the application uses the OCISubscriptionRegister interface to create a registration and specifies a client side C callback as the notification handler. After registration, an event thread is spawned on the client side program in the process. When a transaction changes any of the registered objects and commits, the EMON process of the RDBMS sends the notification to the event thread. The C callback specified by the application is then executed in the context of the event thread.

Registering Queries for Database Change Notification

This section contains the following topics:

Privileges

In order to create a registration for change notification, the user is required to have the CHANGE NOTIFICATION system privilege. In addition the user is required to have SELECT privileges on all objects to be registered. Note that if the SELECT privilege on an object was granted at the time of registration creation but lost subsequently (due to a revoke), then the registration will be purged and a notification to that effect will be published.

What Is a Database Change Registration?

A database change registration can be conceptually thought off as a (recipient R, list-of-queries QL) tuple. The recipient is notified when a change occurs to any of the objects referenced in the queries in the Query List. The recipient can be either a server side PL/SQL stored procedure or a client side C callback. Once created a registration is a persistent entity stored in an Oracle database. It is visible to all instances of an Oracle Real Applications Cluster. Transactions that modify registered objects in any instance of the cluster generate notifications. Once created, the registration survives until explicitly unregistered by the client application or timed-out or implicitly removed by the database for some other reason (such as loss of privileges).

If you have been granted the CHANGE NOTIFICATION privilege in a database session, then you can register a query to receive notifications by performing these steps:

  1. Create the notification recipient for the queries that you want to register. The recipient can be one of the following:

  2. Create an query registration for a specified notification recipient, as described in "Registering Queries for Change Notification Through PL/SQL". You can perform this registration by executing SQL queries. After the SQL execution the registration is complete.

Note:

You must be connected as a non-SYS user and should NOT be in the middle of an uncommitted transaction in order to be able to create a registration.

The dml_locks init.ora parameter must have a nonzero value in order to be able to successfully create registrations and receive notifications.

The default value of dml_locks is nonzero, therefore this requirement is automatically fulfilled if the application does not configure the dml_locks parameter explicitly.

Change Notification Registrations are persistent by default and survive until the application explicitly unregisters them.

After the registration has been successfully created, the Oracle Database notifies client applications in response to any changes to objects referred to in the registered queries, when the underlying transaction commits. Notifications are generated as a result of DML operations like INSERT, UPDATE, and DELETE (on transaction commit) and DDL operations like ALTER and DROP.

The notification includes information on the names of the objects changed, the Transaction-Id of the transaction that made the change and the TYPE of operation (INSERT, UPDATE or DELETE).

Note:

If multiple registered objects were modified in a single transaction, then the application will receive one notification for every modified object when the transaction commits.

Supported Query Types

Change Notification allows the application to register most query types including queries executed as part of stored procedures and REF cursors. When performing a registration, the application is required to be only executing queries, that is, DML or DDL operations are not permitted. In addition, the following types of queries are not supported for registration.

  • Queries on fixed tables or fixed views.

  • Queries with dblinks inside them

  • Queries over materialized views

Registration Properties

Oracle Database supports the following options for an object registration:

  1. Purge On Notify option: Unregistering after the first change notification.

  2. Timeout option: Specification of a registration expiration after a time interval.

  3. ROWIDs option: ROWIDs of changed rows are part of the notification ROWID option.

  4. Reliable Notification option: By default, notifications are generated in shared memory. If this option is chosen, notifications are generated in a persistent database queue. The notifications are enqueued atomically with the transaction that changes a registered object. Since the notifications are persistent in the database, if an instance crashes after generating a notification, they can be delivered when it restarts subsequently OR by a surviving instance of the cluster if running RAC. (Note: there is a trade-off involved here between performance of notifications and reliability. Since there are CPU and I/O costs when generating reliable notifications, it is recommended to choose the default in memory option if better notification performance is desired).

  5. Operations filter: Ability to be notified of PARTICULAR operations (for example notifications only for INSERT AND UPDATE).

  6. Transaction Lag: Specification of a count between successive notifications.

If the ROWID option is chosen, then ROWIDs of changed rows are published as part of the notification. The ROWIDs are published in the external string format. From the ROWID information in the notification, the application should be subsequently able to retrieve the contents of the changed rows by performing a query of the form "SELECT * from table_name_from_notification where ROWID = rowid_from_notification". The length of the ROWID is 18 character bytes in the case of regular heap tables. In the case of Index Organized Tables (IOTs), the length of the ROWID depends on the size of the primary key and therefore could be larger than 18 bytes.

The ROWID notifications are hierarchically summarized. If enough memory is not available on the server side to hold ROWIDs, then the notification might be rolled up into a FULL-TABLE-NOTIFICATION (a special flag in the notification descriptor is reserved for this purpose). When such a notification is received, the application must conservatively assume that the entire table (that is, all rows) may have been modified. ROWIDs are not part of such a notification. ROWIDs may be rolled-up if the total shared memory consumption due to ROWIDs is too large (exceeds 1% of the dynamic shared pool size), OR if too many rows were modified in a single registered object within a transaction (more than 80 approximately) OR if the total length of the logical ROWIDs of modified rows for an IOT is too large (exceeds. 1800 bytes approximately.).

Drop Table

When a table is dropped, a DROP NOTIFICATION is published. Any registrations on the dropped table will implicitly remove interest from that object (since it does not exist anymore). If those registrations have interest in other objects as well, then the registrations will continue to exist and DML transactions on those other objects will continue to result in notifications on commit. Even if the dropped table is the only object of interest for a particular registration, we still preserve the registration. The user that created that registration can use the registration to add more objects/queries subsequently.

A registration is based on the version and definition of an object at the time the query was registered. If an object was dropped, registrations on the object will lose interest on the object forever. Subsequently, even if a different object was created with a matching name and in the same schema, then the newly created object is a new object for the purposes of existing Database Change Notification Registrations, that is, any changes to this newly created object (with the matching schema/name) will not result in notifications for those registrations that existed on the prior version of the object.

Interfaces for Database Change Registration

Registration interfaces are available in both PL/SQL and OCI.

The PL/SQL API enables you to define a registration block. The registration properties including the PL/SQL notification handler are specified during the begin phase of the registration block. Any queries executed inside the registration block are considered interesting queries and the objects referenced in those queries are added to the registration. The registration is completed upon ending the registration block. In PL/SQL, you use the DBMS_CHANGE_NOTIFICATION package to register to receive change notifications.

The OCI Registration APIs involve the invocation of the function OCISubscriptionRegister in a namespace called the DBCHANGE namespace. The registration properties including the client side C notification callback are specified as attributes on the subscription handle. On return from OCISubscriptionRegister, an end-point registration is successfully created in the database. The application can then associate multiple queries with that registration. This can be done by populating the subscription handle as one of the attributes on the statement handle. Registration of objects occurs during statement execution, that is, as part of the OCIStmtExecute call if the statement handle has a subscription handle in the DBCHANGE namespace associated with it.

See Also:

For OCI examples, refer to Oracle Call Interface Programmer's Guide, section "Database Change Notification"

Creating a PL/SQL Stored Procedure as the Change Notification Recipient

You can create a PL/SQL stored procedure that the database server invokes in response to a change to a registered object. The procedure that receives the notification must have the following signature, where schema_name is the name of the database schema and proc_name is the name of the stored procedure:

PROCEDURE schema_name.proc_name( ntfnds IN SYS.CHNF$_DESC )

The JOBQ process passes the CHNF$_DESC object (notification descriptor), whose attributes describe the details of the change, to the callback procedure. For example, the object contains the transaction ID, the type of change that occurred, the tables that were modified, and so forth. The callback procedure can then send this data to a mid-tier client application for further processing.

Note:

The JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of processes that can be created for the execution of jobs. You must set it to a nonzero value to receive PL/SQL notifications because the specified callback procedure is executed inside a job queue process.

See Also:

Registering Queries for Change Notification Through PL/SQL

You must register the database queries for which you want to receive change notifications. The registration occurs in two steps:

  1. Create a CHNF$_REG_INFO object that specifies the name of the callback procedure and other metadata concerning the notification.

  2. Create or update a query registration by executing a program unit in the DBMS_CHANGE_NOTIFICATION package and then execute the queries that you want to register.

Creating a CHNF$_REG_INFO Object

An object of type CHNF$_REG_INFO specifies the callback procedure that the database should execute when one of your registered objects changes. You can view the type attributes in SQL*Plus by running the following command:

DESC SYS.CHNF$_REG_INFO

Table 13-1 provides brief descriptions of the attributes of SYS.CHNF$_REG_INFO.

Table 13-1 Attributes of SYS.CHNF$_REG_INFO

Attribute Description

CALLBACK

Specifies the name of the PL/SQL callback procedure to be executed when a notification is generated. You must specify the name in the form schema_name.procedure_name, for example, hr.dcn_callback.

QOSFLAGS

Specifies one of the following constants in the DBMS_CHANGE_NOTIFICATION package:

  • QOS_RELIABLE, which specifies that notifications persist in the database and survive instance failure. If an instance fails in a Real Applications Cluster, then surviving instances can deliver any queued notification messages. By default, the database buffers change notification messages in shared memory (that is, the messages are not recorded to persistent storage) for better performance.

  • QOS_DEREG_NFY, which specifies that the database should unregister the registration on the first notification.

  • QOS_ROWIDS, which specifies that the notification should include information about the modified ROWIDs.

It is possible to specify a combination of the above options using bitwise OR for example, (dbms_change_notification.QOS_RELIABLE + dbms_change_notification.QOS_ROWIDS)

TIMEOUT

Specifies the timeout period for registrations. If set to a nonzero value, it specifies the time in seconds after which the database purges the registration. If 0 or NULL, then the registration persists until the client explicitly unregisters it.

Note: You can combine the TIMEOUT option with the QOS_DEREG_NFY option in the QOSFLAGS attribute.

OPERATIONS_FILTER

Filters messages based on types of SQL statement. You can specify the following constants in the DBMS_CHANGE_NOTIFICATION package:

  • ALL_OPERATIONS notifies on all changes

  • INSERTOP notifies on inserts

  • UPDATEOP notifies on updates

  • DELETEOP notifies on deletes

You can specify a combination of operations with a bitwise OR. For example, you can perform addition as follows: DBMS_CHANGE_NOTIFICATION.INSERTOP + DBMS_CHANGE_NOTIFICATION.DELETEOP.

TRANSACTION_LAG

Specifies the number of transactions or database changes by which the client can lag behind the database. If 0, then the client receives an invalidation message as soon as it is generated. If 5, then every fifth transaction that changes a registered object results in a notification. Oracle Database tracks intervening changes at an object granularity and bundles the changes along with the notification. Thus, the client does not lose intervening changes.

Note1: Most applications that need to be notified of changes to an object on transaction commit without further deferral would be expected to chose 0 transaction lag. A non-zero transaction lag is useful only if an application wishes to implement some flow control on notifications. When using nonzero transaction lag, it is recommended that the application workload has the property that notifications are generated at a reasonable periodicity in time. Otherwise, notifications maybe deferred indefinitely till the lag is satisfied.

Note2: If you specify TRANSACTION_LAG, then the ROWID level granularity is not available in the notification messages even if you specified QOS_ROWIDS during registration.


Suppose that you want to invoke the procedure hr.dcn_callback whenever a registered object changes. In Example 13-1, you create a CHNF$_REG_INFO object that specifies that hr.dcn_callback should receive change notifications. Note that to create the object you must have EXECUTE privileges on the DBMS_CHANGE_NOTIFICATION package.

Example 13-1 Creating a CHNF$_REG_INFO Object

DECLARE
  v_cn_addr SYS.CHNF$_REG_INFO;
BEGIN
  -- create the object
  v_cn_addr := 
    SYS.CHNF$_REG_INFO
    (
      'hr.dcn_callback­',                    -- name of PL/SQL callback procedure
       DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, -- include rowids of modified objects
       0,                              -- registration persists until unregistered
       0,                              -- notify on all types of DML
       0                               
-- notify immediately (no transaction lag)
    );
  -- ... register objects ...
END;
/

Creating a Registration with DBMS_CHANGE_NOTIFICATION

Use the subprograms in the DBMS_CHANGE_NOTIFICATION package to register queries for a specified notification recipient. For complete documentation for this package, refer to Oracle Database PL/SQL Packages and Types Reference. You can view the package contents in SQL*Plus by connecting as SYS and running the following command:

DESC SYS.DBMS_CHANGE_NOTIFICATION

Table 13-2 provides brief descriptions of the DBMS_CHANGE_NOTIFICATION subprograms.

Table 13-2 DBMS_CHANGE_NOTIFICATION Subprograms

Program Unit Signature Description

NEW_REG_START (reg_info IN SYS.CHNF$_REG_INFO) RETURNS reg_id NUMBER

Marks the beginning of a registration block for inbound object reg_info. The function creates a NEW registration with the properties specified in the reg_info object type. A unique integer identifier called the registration_id is assigned to the registration by the database. The registration_id is returned to the client on return from the function. The application can use the registration_id to keep track of all registrations that were created by it. When a notification is generated for this registration, the registration_id will be part of the notification.

After calling this function, you can execute the queries that you want to register and then end the registration boundary by calling REG_END. You cannot begin a new registration if a registration is currently in progress.

REG_END

Marks the end of the registration boundary that you started with NEW_REG_START or ENABLE_REG. The database does not register any newly executed queries after the call to REG_END.

ENABLE_REG (reg_id IN INTEGER)

Adds a database object to an existing registration_id. This interface is similar to NEW_REG_START, except that it accepts an existing registration_id to which to add objects. Subsequent execution of queries causes the objects referred to in the queries to be added to the registration. Invoke REG_END to terminate the registration.

DEREGISTER (reg_id IN INTEGER)

Disables the registration specified by its registration ID.


For an example of an object registration, suppose that the client requires notification whenever a row changes in the hr.employees table. Example 13-2 shows an anonymous PL/SQL block that registers this table with the hr.dcn_callback procedure. Note that you must have been granted the CHANGE NOTIFICATION privilege to execute this block.

Example 13-2 Registering the Employees Table for Change Notifications

DECLARE
  v_cn_recip       SYS.CHNF$_REG_INFO;
  v_regid          NUMBER;
  v_employee_id    hr.employees.manager_id%TYPE;
BEGIN
  v_cn_recip := SYS.CHNF$_REG_INFO('hr.dcn_callback­', 
                DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, 0, 0, 0);
  -- begin the registration boundary
  v_regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START(v_cn_recip);
    SELECT employee_id 
      INTO   v_employee_id 
    FROM   hr.employees       -- register the employees object
    WHERE  ROWNUM < 2;        -- write the query so that it returns a single row
  -- end the registration boundary
  DBMS_CHANGE_NOTIFICATION.REG_END;
  DBMS_OUTPUT.PUT_LINE('the registration id for this query is '||v_regid);
END;
/

In Example 13-2, the program registers the object itself, that is, the hr.employees table. The WHERE clause restricts the query to the first employee in the result set to avoid generating an error because the fetch returns multiple rows. The DBMS_CHANGE_NOTIFICATION package registers the object itself, which means that any change to the table—regardless of whether the change is to the row returned by the registered query—generates a notification.

Adding Objects to an Existing Registration

Suppose that later you decide to add a query against the hr.departments table to the registration ID for the hr.employees query. After you retrieve the registration ID either from the saved SQL*Plus output or a query of USER_CHANGE_NOTIFICATION_REGS, you can add this object with the code in Example 13-3 by substituting the numeric ID for reg_id.

Example 13-3 Adding an Object to an Existing Registration

DECLARE
  v_department_id     hr.departments.department_id%TYPE;
BEGIN
  -- begin registration boundary
  DBMS_CHANGE_NOTIFICATION.ENABLE_REG(reg_id);
    SELECT department_id
      INTO   v_department_id 
    FROM   hr.departments
    WHERE  ROWNUM < 2; -- register this query
  -- end registration boundary
  DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/

Querying Change Notification Registrations

You can query the following data dictionary views to obtain information about registered clients of the Database Change Notification feature:

  • DBA_CHANGE_NOTIFICATION_REGS

  • USER_CHANGE_NOTIFICATION_REGS

For example, you can obtain the registration ID for a client and the list of objects for which it receives notifications. To view registration-ids and table names for HR, you can do the following from SQL*Plus:

connect hr/hr;
SELECT regid, table_name FROM USER_CHANGE_NOTIFICATION_REGS;

See Also:

Oracle Database Reference for descriptions of DBA_CHANGE_NOTIFICATION_REGS and USER_CHANGE_NOTIFICATION_REGS

Interpreting a Database Change Notification

When a transaction commits, Oracle Database determines whether registered objects were modified in the transaction. If the database finds interested clients, it executes the callback procedure specified in the registration.

Interpreting a CHNF$_DESC Object

The details of a database change are exposed through descriptors that the database pass as arguments to a C callback or PL/SQL procedure. Specifically, Oracle Database passes an object of type CHNF$_DESC, which is the top-level change notification descriptor. You can view the type attributes in SQL*Plus by connecting as SYS and running the following command:

DESC SYS.CHNF$_DESC

Table 13-3 provides brief descriptions of the attributes of CHNF$_DESC.

Table 13-3 Attributes of SYS.CHNF$_DESC

Attribute Specifies . . .

REGISTRATION_ID

The registration ID that was returned during registration.

TRANSACTION_ID

The ID for the transaction that made the change.

DBNAME

The name of the database in which the changed objects reside.

EVENT_TYPE

The database event that triggers a notification. For example, the attribute can contain the following constants, which correspond to different database events:

  • EVENT_NONE

  • EVENT_STARTUP (Instance startup)

  • EVENT_SHUTDOWN (Instance shutdown - last instance shutdown in the case of RAC)

  • EVENT_SHUTDOWN_ANY (Any instance shutdown in the case of RAC)

  • EVENT_DEREG (Registration has been removed)

  • EVENT_OBJCHANGE (Change to a registered table)

NUMTABLES

The number of tables that were modified.

TABLE_DESC_ARRAY

A varray of table change descriptors of type CHNF$_TDESC, which is described in Table 13-4. Each table descriptor corresponds to a table that was modified.


Interpreting a CHNF$_TDESC Object

The CHNF$_DESC type contains an attribute called TABLE_DESC_ARRAY, which holds an array of table descriptors of type CHNF$_TDESC. You can view the type attributes in SQL*Plus by connecting as SYS and running the following command:

DESC CHNF$_TDESC

Table 13-4 provides brief descriptions of the attributes of CHNF$_TDESC.

Table 13-4 Attributes of SYS.CHNF$_TDESC

Attribute Specifies . . .

OPFLAGS

The type of operation performed on the modified table. For example, the attribute can contain the following constants, which correspond to different database operations:

  • ALL_ROWS signifies that either the entire table is modified, as in a DELETE *, or row-level granularity of information is not requested or not available in the notification, and the recipient must assume that the entire table has changed

  • UPDATEOP signifies an update

  • DELETEOP signifies a deletion

  • ALTEROP signifies an ALTER TABLE

  • DROPOP signifies a DROP TABLE

  • UNKNOWNOP signifies an unknown operation

TABLE_NAME

The name of the modified table.

NUMROWS

The number of modified rows.

ROW_DESC_ARRAY

A varray of row descriptors of type CHNF$_RDESC, which is described in Table 13-5. If ALL_ROWS was set in the opflags, then the desc_array member is NULL.


Interpreting a CHNF$_RDESC Object

If the ROWID option was chosen during registration, the CHNF$_TDESC type in turn holds an array of type CHNF$_RDESC, which contains the ROWIDs for the changed rows. Note: if ALL_ROWS was set in the opflags field of the CHNF$_TDESC object, then ROWID information is not available.


Table 13-5 provides brief descriptions of the attributes of CHNF$_RDESC.

Table 13-5 Attributes of SYS.CHNF$_RDESC

Attribute Specifies . . .

OPFLAGS

The type of operation performed on the modified table. See the description of OPFLAGS in Table 13-4.

ROW_ID

The ROWID of the changed row.


Configuring Database Change Notification: Scenario

In this scenario, you are a developer who manages a Web application that provides employee data: name, location, phone number, and so forth. The application, which runs on Oracle Application Server, is heavily used and processes frequent queries of the hr.employees and hr.departments tables in the back-end database. Because these tables change relatively infrequently, the application can improve performance by caching table rows. Caching avoids a round trip to the back-end database as well as server-side execution latency.

You can use the DBMS_CHANGE_NOTIFICATION package to register a query based on hr.employees and hr.departments tables. To configure database change notification, you follow these steps:

  1. Implement a mid-tier HTTP listener that listens for notifications and updates the mid-tier cache in response to a notification of a change to the hr.employees and hr.departments tables

  2. Create a server-side PL/SQL stored procedure to process the change notifications, as described in "Creating a PL/SQL Callback Procedure"

  3. Register the hr.employees and hr.departments tables, as described in "Registering the Query"

After you complete these steps, the server-side PL/SQL procedure defined in step 2 executes in response to changes to hr.employees or hr.departments. The callback procedure notifies the Web application of the tables changed. In response to the notification, the application refreshes the cache by querying the back-end database.

Creating a PL/SQL Callback Procedure

In this step, you write a server-side stored procedure to process change notifications. You first connect to the database as a user with DBA privileges and grant EXECUTE privileges to hr:

GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO HR;
GRANT CHANGE NOTIFICATION TO HR;

Enable the job_queue_processes parameter to receive notifications:

ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4;

You may want to create database tables to hold the record of notification events received:

connect hr/hr;
Rem Create a table to record notification events
CREATE table nfevents(regid number, event_type number);
Rem Create a table to record changes to registered tables
create table nftablechanges(
       regid number, 
       table_name varchar2(100),
       table_operation number);
Rem Create a table to record rowids of changed rows.
create table nfrowchanges(
       regid number, 
       table_name varchar2(100), 
       row_id varchar2(2000));
       

You then create the procedure hr.chnf_callback, as shown in Example 13-4.

Example 13-4 Server-Side PL/SQL Callback Procedure

CREATE OR REPLACE PROCEDURE chnf_callback(ntfnds IN SYS.CHNF$_DESC) IS
regid NUMBER;
tbname VARCHAR2(60);
event_type NUMBER;
numtables NUMBER;
operation_type NUMBER;
numrows NUMBER;
row_id VARCHAR2(2000);
 
BEGIN
  regid := ntfnds.registration_id;
  numtables := ntfnds.numtables;
  event_type := ntfnds.event_type;
  insert into nfevents values(regid, event_type);
  
  IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
    FOR i IN 1..numtables LOOP
      tbname := ntfnds.table_desc_array(i).table_name;
      operation_type := ntfnds.table_desc_array(I). Opflags;
      insert into nftablechanges values(regid, tbname, operation_type);
      /* Send the table name and operation_type to  client side listener 
         using UTL_HTTP */                          
      /* If interested in the rowids, obtain them as follows */
      IF (bitand(operation_type, dbms_change_notification.ALL_ROWS) = 0)
      THEN     
        numrows := ntfnds.table_desc_array(i).numrows;
      else 
        numrows :=0;   /* ROWID INFO NOT AVAILABLE */
      END IF;
      
      /* The body of the loop is not executed when numrows is ZERO */
      FOR j IN 1..numrows LOOP
          Row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
          insert into nfrowchanges values(regid, tbname, Row_id);
          /* optionally Send out row_ids to client side listener using
             UTL_HTTP */
      END LOOP;
      
    END LOOP;
  END IF;
  commit;
END;
/

Registering the Query

After you have created the callback procedure, you register the query based on the tables for which you want to receive notifications. In Example 13-5, you pass in hr.chnf_callback as the name of the server-side PL/SQL procedure to be executed when the database generates a notification. Note that you must have the CHANGE NOTIFICATION privilege to create the procedure.

Example 13-5 Table Registration

CREATE OR REPLACE PROCEDURE hr.table_reg
IS
  v_regds          SYS.CHNF$_REG_INFO;
  v_regid          NUMBER;
  v_employee_id    NUMBER;  v_department_id  NUMBER;
BEGIN
  v_regds := SYS.CHNF$_REG_INFO ('hr.chnf_callback',  
                             DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, 
                             0,
                             0,
                             0);
  v_regid :=  DBMS_CHANGE_NOTIFICATION.NEW_REG_START(v_regds);
    SELECT employee_id
      INTO   v_employee_id
    FROM   hr.employees     -- register employees object
    WHERE ROWNUM < 2;       -- return a single row to avoid multiple fetch error
    SELECT department_id
      INTO   v_department_id
    FROM   hr.departments   -- register departments object
    WHERE  ROWNUM < 2;      -- return a single row to avoid multiple fetch error
  DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/

EXEC hr.table_reg

You can view the newly created registration by issuing the following query:

SQL> select regid, table_name from user_change_notification_regs;
     REGID TABLE_NAME
---------- -------------------------------------------------------------
        16 HR.EMPLOYEES
        16 HR.DEPARTMENTS

Once the registration is created as shown above, the server side PL/SQL procedure chnf_callback, as described above, is executed in response to any committed changes to the HR.EMPLOYEES or HR.DEPARTMENTS tables. As an example, let us assume that the following update is performed on the employees table:

UPDATE employees SET salary=salary*1.05 WHERE employee_id=203;
COMMIT;

Once the notification is processed, you will find rows which might look like the following in the nfevents, nftablechanges, and nfrowchanges tables:

SQL> select * from nfevents;
     REGID EVENT_TYPE
     ---------- ----------
     20045          6

SQL> select * from nftablechanges;
     REGID      TABLE_NAME         TABLE_OPERATION
     -------------------------------------------
     20045   HR.EMPLOYEES            4

SQL> select * from nfrowchanges;
     REGID  TABLE_NAME   ROW_ID
     ---------------------------------------------
     20045 HR.EMPLOYEES   AAAKB/AABAAAJ8zAAF

Best Practices

For best performance of change notification, the following guidelines are presented.Registered objects are few and mostly read-only and that modifications to those objects are the exception rather than the rule. If the object is extremely volatile, then it will cause a large number of invalidation notifications to be sent, and potentially a lot of storage in the invalidation queue on the server. If there are frequent and a large number of notifications, it can slow down OLTP throughput due to the overhead of generating the notifications.It is also a good idea to keep the number of duplicate registrations on any given object low (ideally one) in order to avoid the same notification message being replicated to multiple recipients.

Troubleshooting

If you have created a registration and seem to not receive notifications or you are unable to create a registration, the following is a list of things to check for.

  1. Is the job_queue_processes parameter set to a nonzero value? This parameter needs to be configured to a nonzero value in order to receive PL/SQL notifications via the handler.

  2. Are the registrations being created as a NON-SYS user?

  3. If you are attempting DML changes on the registered object, are you committing the transaction? Note that the notifications are transactional and will be generated when the transaction commits.

  4. To check that the registrations on the objects have been successfully created in the database, you can query from the USER_CHANGE_NOTIFICATION_REGS or DBA_CHANGE_NOTIFICATION_REGS views. For example, to view all registrations and the registered objects for the current user, you can issue the following select:

    SELECT regid, table_name FROM user_change_notification_regs;
    
    
  5. It maybe possible that there are run-time errors during the execution of the PL/SQL callback due to implementation errors in the callback. If so, they would be logged to the trace file of the JOBQ process that attempts to execute the procedure. The trace file would be usually named <ORACLE_SID>_j*_<PID>.trc. For example, if the ORACLE_SID is 'dbs1' and the process id of the JOBQ process is 12483, the trace file might be named 'dbs1_j000_12483.trc'.

    If there are run-time errors, then it will be reported to the JOBQ trace file. For example, let's say a registration is created with 'chnf_callback' as the notification handler and registration id 100. Let's say the 'chnf_callback' stored procedure was not DEFINED in the database. Then the JOBQ trace file might contain a message of the form:

    *************************************************************************** 
       Runtime error during execution of PL/SQL cbk chnf_callback for reg CHNF100.
       Error in PLSQL notification of msgid:
       Queue : 
       Consumer Name :
       PLSQL function :chnf_callback
       Exception Occured, Error msg:
       ORA-00604: error occurred at recursive SQL level 2
       ORA-06550: line 1, column 7: 
       PLS-00201: identifier 'CHNF_CALLBACK' must be declared
       ORA-06550: line 1, column 7:
       PL/SQL: Statement ignored 
       ****************************************************************************
    
    
  6. If you are running into errors during the execution of the callback, consider creating a very simple version of the callback as shown below to verify that you are actually receiving notifications. The callback can be gradually evolved to add more application logic. For example, if the user is HR then you might consider creating a very simple version of the notification handler as follows:

    Rem create a table in the HR schema to hold a count of number of notifications received.
    Create table nfcount(cnt number);
    Insert into nfcount values(0);
    Commit;
    CREATE OR REPLACE PROCEDURE chnf_callback (ntfnds IN SYS.CHNF$_DESC)
    IS
    BEGIN
      update nfcount set cnt = cnt+1;
      commit;
    END;
    /
    

    The simple procedure created increments the count column of a table and commits. To verify that notifications are being published, you can query from the table nfcount to see if the cnt column is indeed going up when a change is made to a registered object and the transaction committed.

  7. There maybe a time lag between the commit of a transaction and the notification received by the end user.