23 DBMS_CHANGE_NOTIFICATION

Using DBMS_CHANGE_NOTIFICATION

• Overview

• Security Model

• Constants

• Operational Notes

• Examples

---

Overview

The DBMS_CHANGE_NOTIFICATION package provides PL/SQL based registration interfaces. A client can use this interface to create registrations on queries based on objects of interest and specify a PL/SQL callback handler to receive notifications. When a transaction changes any of the objects associated with the registered queries and COMMITs, this invokes the PL/SQL callback specified during registration. The application can define client-specific processing inside the implementation of its PL/SQL callback handler.

The interface lets you define a registration block (using a mechanism similar to a BEGIN-END block). The recipient of notifications namely the name of the PL/SQL callback handler and a few other registration properties like time-outs can be specified during the BEGIN phase. Any queries executed subsequently (inside the registration block) are considered "interesting queries" and objects referenced by those queries during query execution are registered. The registration is completed by ENDing the registration block.The registration block lets you create new registrations or add objects to existing registrations.

When a registration is created through the PL/SQL interface, a unique registration ID is assigned to the registration by the RDBMS. The client application can use the registration ID to keep track of registrations created by it. When a notification is published by the RDBMS, the registration ID will be part of the notification.

Typical Applications

This functionality is useful for example to applications that cache 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. On changes to objects referenced inside those queries, the database publishes a notification when the underlying transaction commits. In response to the notification, the mid-tier application can refresh its cache by re-executing the query/queries.

---

Security Model

The DBMS_CHANGE_NOTIFICATION package requires that the user have the CHANGE NOTIFICATION system privilege in order to receive notifications, and be granted EXECUTE privilege on the DBMS_CHANGE_NOTIFICATION package.

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.

---

Constants

The DBMS_CHANGE_NOTIFICATION package uses the constants shown in Table 23-1. The constants are used as flag parameters either during registration or when received during the notification.

The DBMS_CHANGE_NOTIFCATION package has sets of constants:

• EVENT_STARTUP, EVENT_SHUTDOWN, EVENT_SHUTDOWN_ANY, EVENT_DEREG describe the type of the notification published by the database.

• INSERTOP, DELETEOP, UPDATEOP, ALTEROP, DROPOP and UNKNOWNOP describe the type of operation on a table (during a notification published by the database).

• QOS_RELIABLE, QOS_DEREG_NFY, QOS_ROWIDs describe registration Quality of Service properties that the client requires. These are specified during registration.

---

Operational Notes

• The notifications are published by the database when a transaction changes the registered objects and COMMITs.

• All objects referenced in the queries executed inside the registration block starting from the previous NEW_REG_START or ENABLE_REG to REG_END are considered interesting objects and added to the registration.

Troubleshooting

If you have created a registration and seem to not receive notifications when the underlying tables are changed, please check the following.

• Is the job_queue_processes parameter set to a non-zero value? This parameter needs to be configured to a non-zero value in order to receive PL/SQL notifications via the handler.

• Are the registrations being created as a non-SYS user?

• If you are attempting DML changes on the registered object, are you COMMITing the transaction? Please note that the notifications are transactional and will be generated when the transaction COMMITs.

• It maybe possible that there are run-time errors during the execution of the PL/SQL callback due to implementation errors. 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 is 12483, the trace file might be named 'dbs1_j000_12483.trc.

  Suppose a registration is created with 'chnf_callback as the notification handler and with registration_id 100. Let us suppose the user forgets to define the chnf_callback procedure. Then the JOBQ trace file might contain a message of the following 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 
  

---

Examples

Suppose that a mid-tier application has a lot of queries on the HR.EMPLOYEES table. If the EMPLOYEES table is infrequently updated, it can obtain better performance by caching rows from the table because that would avoid a round-trip to the backend database server and server side execution latency. Let us assume that the application has implemented a mid-tier HTTP listener that listens for notifications and updates the mid-tier cache in response to a notification.

The DBMS_CHANGE_NOTIFICATION package can be utilized in this scenario to send notifications about changes to the table by means of the following steps:

1. Implement a mid-tier listener component of the cache management system (for example, using HTTP) that listens to notification messages sent from the database and refreshes the mid-tier cache in response to the notification.

2. Create a server side stored procedure to process notifications

CONNECT / AS SYSDBA;
GRANT CHANGE NOTIFICATION TO hr;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO hr;
 
Rem Enable job queue processes to receive notifications.
ALTER SYSTEM SET "job_queue_processes"=2;
 
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(30));
 
Rem Create a PL/SQL callback handler to process notifications.
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(20);
 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;
/ 

In Step 2 we can send as much information about the invalidation as the mid-tier application needs based on the information obtained from the notification descriptor.

3. Create a registrations on the tables that we wish to be notified about. We pass in the previously defined procedure name (chnf_callback) as the name of the server side PL/SQL procedure to be executed when a notification is generated.

Rem Create a REGISTRATION on the EMPLOYEES TABLE    
DECLARE
  REGDS      SYS.CHNF$_REG_INFO;
  regid      NUMBER;
  mgr_id     NUMBER;
  dept_id    NUMBER;
  qosflags   NUMBER;
BEGIN
 qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE +
         DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
REGDS := SYS.CHNF$_REG_INFO ('chnf_callback', qosflags, 0,0,0);
regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS); 
SELECT manager_id INTO mgr_id FROM EMPLOYEES WHERE employee_id = 200;
DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/

Once the registration is created in Step 3 above, the server side PL/SQL procedure defined in Step 2 is executed in response to any COMMITted changes to the HR.EMPLOYEES table. 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

Notes

1. In the above example, a registration was created on the EMPLOYEES table with 'chnf_callback' as the PL/SQL handler for notifications. During registration, the client specified reliable notifications (QOS_RELIABLE) and rowid notifications (QOS_ROWIDS)

2. The handler accesses the table descriptor array from the notification descriptor only if the notification type is of EVENT_OBJCHANGE. In all other cases (e.g EVENT_DEREG, EVENT_SHUTDOWN), the table descriptor array should not be accessed.

3. The handler accesses the row descriptor array from the table notification descriptor only if the ALL_ROWS bit is not set in the table operation flag. If the ALL_ROWS bit is set in the table operation flag, then it means that all rows within the table may have been potentially modified. In addition to operations like TRUNCATE that affect all rows in the tables, this bit may also be set if individual rowids have been rolled up into a FULL table invalidation.

   This can occur if too many rows were modified on a given table in a single transaction (more than 80) or the total shared memory consumption due to rowids on the RDBMS is determined too large (exceeds 1 % of the dynamic shared pool size). In this case, the recipient must conservatively assume that the entire table has been invalidated and the callback/application must be able to handle this condition.

   Also note that the implementation of the user defined callback is up to the developer. In the above example, the callback was used to record event details into database tables. The application can additionally send the notification details to a mid-tier HTTP listener of its cache management system (as in the example) using UTL_HTTP. The listener could then refresh its cache by querying from the back-end database.

Data Structures

The DBMS_CHANGE_NOTIFICATION package uses the following OBJECT types.

OBJECT Types

• SYS.CHNF$_DESC Object Type

• SYS.CHNF$_TDESC Object Type

• SYS.CHNF$_TDESC_ARRAY Object (Array) Type

• SYS.CHNF$_RDESC Object Type

• SYS.CHNF$_RDESC_ARRAY Object (Array) Type

• SYS.CHNF$_REG_INFO Object Type

---

SYS.CHNF$_DESC Object Type

This is the top level change notification descriptor type.

Syntax

TYPE SYS.CHNF$_DESC IS OBJECT(
   registration_id    NUMBER,
   transaction_id     RAW(8),
   dbname             VARCHAR2(30),
   event_type         NUMBER,
   numtables          NUMBER,
   table_desc_array   CHNF$_TDESC_ARRAY)

Attributes

---

SYS.CHNF$_TDESC Object Type

This object type is the table descriptor that contains an array of row descriptors.

Syntax

TYPE SYS.CHNF$_TDESC IS OBJECT OF (
   opflags         NUMBER,
   table_name      VARCHAR2(64),
   numrows         NUMBER, 
   row_desc_array  CHNF$_RDESC_ARRAY);

Attributes

---

SYS.CHNF$_TDESC_ARRAY Object (Array) Type

This object is the table descriptor type that describes the operations that occurred on a registered table.

Syntax

TYPE SYS.CHNF$_TDESC_ARRAY IS VARRAY (1024) OF CHNF$_TDESC;

---

SYS.CHNF$_RDESC Object Type

This object type is the notification descriptor received as an argument of the server side PL/SQL procedure which contains the details of the invalidation. This object type describes modifications to an individual row within a changed table.

An array of CHNF$_RDESC is embedded inside a CHNF$_TDESC (table change descriptor) if the QOS_ROWIDS option was chosen at the time of registration and the ALL_ROWS bit is not set in the opflags field of the table change descriptor.

Syntax

TYPE SYS.CHNF$_RDESC IS OBJECT OF (
   opflags     NUMBER,
   row_id      VARCHAR2(2000));

Attributes

---

SYS.CHNF$_RDESC_ARRAY Object (Array) Type

This object type corresponds to an array of row change notification descriptors and is embedded inside the table change descriptor (CHNF$_TDESC) if QOS_ROWIDS was specified during registration and the ALL_ROWS bit is not set in the opflags field of the table change descriptor.

Syntax

TYPE SYS.CHNF$_RDESC_ARRAY IS VARRAY (1024) OF CHNF$_RDESC;

---

SYS.CHNF$_REG_INFO Object Type

The object type describes the attributes associated with creating a new registration.

Syntax

TYPE SYS.CHNF$_REG_INFO IS OBJECT (
  callback                        VARCHAR2(20),
  quosflags                       NUMBER,
  timeout                         NUMBER,
  operations_filter               NUMBER,
  transaction_lag                 NUMBER);

Attributes

Usage Notes

• In response to a database change, the server side PL/SQL procedure specified by "callback" is executed. The PL/SQL procedure name has to be specified in the format schema_name.procedure_name. The procedure must have the following signature:

  PROCEDURE <procedure_name>(ntfnds IN SYS.chnf$_desc)
  
  

  CHNF$_DESC describes the change notification descriptor.

• The init.ora parameter job_queue_processes must be set to a non-zero value to receive PL/SQL notifications, because the specified procedure is executed inside a job queue process when a notification is generated.

Summary of DBMS_CHANGE_NOTIFICATION Subprograms

---

DEREGISTER Procedure

This procedure desubscribes the client with the specified registration identifier (ID).

Syntax

DBMS_CHANGE_NOTIFICATION.DEREGISTER (
  regid IN NUMBER);

Parameters

Usage Notes

Only the user that created the registration (or the SYS user) will be able to desubscribe the registration.

---

ENABLE_REG Procedure

This procedure adds objects to an existing registration identifier (ID). This subprogram is similar to the interface for creating a new registration, except that it takes an existing regid to which to add objects.Subsequent execution of queries causes the objects referenced in the queries to be added to the specified regid, and the registration is completed on invoking the REG_END Procedure.

Syntax

DBMS_CHANGE_NOTIFICATION.ENABLE_REG (  
  regid IN NUMBER);

Parameters

Usage Notes

Only the user that created the registration will be able to add further objects to the registration.

---

NEW_REG_START Function

This procedure begins a new registration block. Any objects referenced by queries executed within the registration block are considered interesting objects and added to the registration. The registration block ends upon calling the REG_END procedure.

Syntax

DBMS_CHANGE_NOTIFICATION.NEW_REG_START (  
  regds IN sys.chnf$_reg_info)
 RETURN NUMBER;

Parameters

Return Values

The procedure returns a registration-id which is a unique integer assigned by the database to this registration. The registration-id will be echoed back in every notification received for this registration.

Usage Notes

• The only operations permitted inside a registration block are queries (the ones the user wishes to register). DML and DDL operations are not permitted.

• The registration block is a session property and implicitly terminates upon exiting the session. While the registration block is a session property, the registration itself is a persistent database entity. Once created, the registration survives until explicitly deregistered by the client application or timed-out or removed by the database for some other reason (such as loss of privileges).

• The user must have the CHANGE NOTIFICATION system privilege and SELECT privileges on any objects to be registered.

• The SYS user will not be permitted to create new registrations.

• Nesting of registration block is not permitted.

---

REG_END Procedure

This procedure marks the end of the registration block. No newly executed queries are tracked.

Syntax

DBMS_CHANGE_NOTIFICATION.REG_END;