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.

In addition the user is required to have SELECT or READ privileges on all objects to be registered. Note that if the SELECT or READ 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.

The DBMS_CQ_NOTIFICATION 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.

The constants are shown in the following table.

Object Level Registration

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

Query Result Change Registration

• The notifications are published by the database when a transaction changes the result set of the registered query and COMMITs.

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 nonzero value? This parameter needs to be configured to a nonzero value in order to receive PL/SQL notifications through 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 

Object Change Registration Example

The DBMS_CQ_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 system;
   Enter password: password
   GRANT CHANGE NOTIFICATION TO hr;
   GRANT EXECUTE ON DBMS_CQ_NOTIFICATION TO hr;
    
   Rem Enable job queue processes to receive notifications.
   ALTER SYSTEM SET "job_queue_processes"=2;
    
   CONNECT hr;
   Enter password: password
   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_CQ_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_CQ_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.

   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.

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_CQ_NOTIFICATION.QOS_RELIABLE +
            DBMS_CQ_NOTIFICATION.QOS_ROWIDS;
   REGDS := SYS.CHNF$_REG_INFO ('chnf_callback', qosflags, 0,0,0);
   regid := DBMS_CQ_NOTIFICATION.NEW_REG_START (REGDS); 
   SELECT manager_id INTO mgr_id FROM EMPLOYEES WHERE employee_id = 200;
   DBMS_CQ_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
   
   

Query Result Change Registration Example

1. Creating a Callback

   CONNECT system;
   Enter password: password
   GRANT CHANGE NOTIFICATION TO hr;
   GRANT EXECUTE ON DBMS_CQ_NOTIFICATION TO hr;
   CONNECT hr;
   Enter password: password
   Rem Create a table to record notification events
   CREATE TABLE nfevents(regid NUMBER, event_type NUMBER);
   
   Rem Create a table to record notification queries
   CREATE TABLE nfqueries (qid NUMBER, qop NUMBER);
   
   Rem Create a table to record changes to registered tables
   CREATE TABLE nftablechanges(
          qid                NUMBER,
          table_name         VARCHAR2(100),
          table_operation    NUMBER);
   
   Rem Create a table to record rowids of changed rows.
   CREATE TABLE nfrowchanges(
          qid                NUMBER,
          table_name         VARCHAR2(100),
          row_id             VARCHAR2(2000));
    
    
   CREATE OR REPLACE PROCEDURE chnf_callback
     (ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR)
   IS
     regid           NUMBER;
     tbname          VARCHAR2(60);
     event_type      NUMBER;
     numtables       NUMBER;
     operation_type  NUMBER;
     numrows         NUMBER;
     row_id          VARCHAR2(2000);
     numqueries      NUMBER;
     qid NUMBER;
     qop NUMBER;
   
   BEGIN
     regid := ntfnds.registration_id;
     event_type := ntfnds.event_type;
     INSERT INTO nfevents VALUES(regid, event_type);
     numqueries :=0;
     IF (event_type = DBMS_CQ_NOTIFICATION.EVENT_QUERYCHANGE) THEN
         numqueries := ntfnds.query_desc_array.count;
         FOR i in 1..numqueries LOOP
            qid := ntfnds.QUERY_DESC_ARRAY(i).queryid;
            qop := ntfnds.QUERY_DESC_ARRAY(i).queryop;
            INSERT INTO nfqueries VALUES(qid, qop);
            numtables := 0;
            numtables := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array.count;
            FOR j IN 1..numtables LOOP
              tbname := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array(j).table_name;
              operation_type := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array(j).Opflags;
              INSERT INTO nftablechanges VALUES(qid, tbname, operation_type);
              IF (bitand(operation_type, DBMS_CQ_NOTIFICATION.ALL_ROWS) = 0)
              THEN
                numrows := ntfnds.query_desc_array(i).table_desc_array(j).numrows;
              ELSE
                numrows :=0;   /* ROWID INFO NOT AVAILABLE */
              END IF;
    
               /* The body of the loop is not executed when numrows is ZERO */
               FOR k IN 1..numrows LOOP
                  Row_id := ntfnds.query_desc_array(i).table_desc_array(j).row_desc_array(k).row_id;
                  INSERT INTO nfrowchanges VALUES(qid, tbname, Row_id);
                  
               END LOOP;  /* loop over rows */
            END LOOP;     /* loop over tables */
          END LOOP;        /* loop over queries */
     END IF;
     COMMIT;
   END;
   /
   
   

2. Creates a query registration

   DECLARE
     reginfo    cq_notification$_reg_info;
     mgr_id     NUMBER;
     dept_id    NUMBER;
     v_cursor   SYS_REFCURSOR;
     regid      NUMBER;
     qosflags   NUMBER;  
   
   BEGIN
     /* Register two queries for result-set-change notifications: */
   
     /* 1. Construct registration information.
           'chnf_callback' is name of notification handler.
           QOS_QUERY specifies result-set-change notifications. */
    
      qosflags := DBMS_CQ_NOTIFICATION.QOS_QUERY + 
                  DBMS_CQ_NOTIFICATION.QOS_ROWIDS;
   
      reginfo := cq_notification$_reg_info('chnf_callback', qosflags,0, 0, 0);
    
     /* 2. Create registration */
    
           regid := DBMS_CQ_NOTIFICATION.NEW_REG_START(reginfo);
    
           OPEN v_cursor FOR 
              SELECT DBMS_CQ_NOTIFICATION.CQ_NOTIFICATION_QUERYID, manager_id
              FROM HR.employees
              WHERE employee_id = 7902;
           CLOSE v_cursor;
    
          OPEN v_cursor for
           SELECT DBMS_CQ_NOTIFICATION.CQ_NOTIFICATION_QUERYID, department_id
           FROM HR.departments
           WHERE department_name = 'IT';
          CLOSE v_cursor;
    
     DBMS_CQ_NOTIFICATION.REG_END;
   END;
   /
   

3. After creating the query registrations, the output from USER_CQ_NOTIFICATION_QUERIES would appear as follows.

   SQL> SELECT queryid, regid, to_char(querytext) 
        FROM user_cq_notification_queries;
    
      QUERYID      REGID
   ---------- ----------
   TO_CHAR(QUERYTEXT)
   --------------------------------------------------------------------------------
           22         41
    SELECT HR.DEPARTMENTS.DEPARTMENT_ID  FROM HR.DEPARTMENTS  WHERE HR.DEPARTMENTS.
   DEPARTMENT_NAME  = 'IT'
    
           21         41
    SELECT HR.EMPLOYEES.MANAGER_ID  FROM HR.EMPLOYEES  WHERE HR.EMPLOYEES.EMPLOYEE_
   ID  = 7902
    
    
   Now, let us perform an UPDATE that changes the result of the query with queryid 22
   by renaming the department with name 'IT' to FINANCE.
    
   SQL>  update departments set department_name = 'FINANCE' where department_name = 'IT';
    
   1 row updated.
    
   SQL> commit;
    
   Commit complete.
    
   Now we can query the notifications that we recorded in the callback.
    
   SQL> select * from nfevents;
    
        REGID EVENT_TYPE
   ---------- ----------
           61          7
    
    
   Event type 7 corresponds to EVENT_QUERYCHANGE
    
   SQL> select * from nfqueries;
    
          QID        QOP
   ---------- ----------
           42          7
    
   Event type 7 corresponds to EVENT_QUERYCHANGE
    
   SQL> select * from nftablechanges;
   SQL> select * from nftablechanges;
    
        REGID
   ----------
   TABLE_NAME
   --------------------------------------------------------------------------------
   TABLE_OPERATION
   ---------------
           42
   HR.DEPARTMENTS
                 4
    
    
   TABLE_OPERATION 4 corresponds to UPDATEOP
    
   SQL> select * from nfrowchanges;
        REGID
   ----------
   TABLE_NAME
   --------------------------------------------------------------------------------
   ROW_ID
   --------------------------------------------------------------------------------
           61
   HR.DEPARTMENTS
   AAANkdAABAAALinAAF

OBJECT Types

• CQ_NOTIFICATION$_DESCRIPTOR Object Type

• CQ_NOTIFICATION$_QUERY Object Type

• CQ_NOTIFICATION$_QUERY_ARRAY Object (Array) Type

• CQ_NOTIFICATION$_TABLE Object Type

• CQ_NOTIFICATION$_TABLE_ARRAY Object (Array) Type

• CQ_NOTIFICATION$_ROW Object Type

• CQ_NOTIFICATION$_ROW_ARRAY Object (Array) Type

• CQ_NOTIFICATION$_REG_INFO Object Type