13 Developing Applications with Database Change Notification

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:

   • PL/SQL stored procedure, as described in "Creating a PL/SQL Stored Procedure as the Change Notification Recipient"

   • OCI callback function, as described in Oracle Call Interface Programmer's Guide

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.

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).

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.

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.

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.

DESC SYS.CHNF$_REG_INFO

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;
/

DESC SYS.DBMS_CHANGE_NOTIFICATION

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;
/

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;
/

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.

DESC CHNF$_TDESC

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.

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.

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