Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

23 DBMS_CHANGE_NOTIFICATION

The DBMS_CHANGE_NOTIFICATION package is part of the database change notification feature that provides the functionality to create registration on queries designated by a client application and so to 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.

See Also:

Oracle Database Application Developer's Guide - Fundamentals regarding implementing database change notification.

This chapter contains the following topics:


Using DBMS_CHANGE_NOTIFICATION


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:

Table 23-1 DBMS_CHANGE_NOTIFICATION Constants

Name Type Value Description

ALL_OPERATIONS

BINARY_INTEGER

0

Interested in being notified on all operations, specified as a parameter during registration

ALL_ROWS BINARY_INTEGER

BINARY_INTEGER

1

All rows within the table may have been potentially modified

EVENT_STARTUP

BINARY_INTEGER

1

Instance startup notification

EVENT_SHUTDOWN

BINARY_INTEGER

2

Instance shutdown notification

EVENT_SHUTDOWN_ANY

BINARY_INTEGER

3

Any instance shutdown when running RAC

EVENT_DEREG

BINARY_INTEGER

5

Registration has been removed

EVENT_OBJCHANGE

BINARY_INTEGER

6

Notification for object change

INSERTOP

BINARY_INTEGER

2

Insert operation

UPDATEOP

BINARY_INTEGER

4

Update operation

DELETEOP

BINARY_INTEGER

8

Delete operation

ALTEROP

BINARY_INTEGER

16

Table altered

DROPOP

BINARY_INTEGER

32

Table dropped

UNKNOWNOP

BINARY_INTEGER

64

Unknown operation

QOS_RELIABLE

BINARY_INTEGER

1

Reliable or persistent notification. Also implies that the notifications will be inserted into the persistent storage atomically with the committing transaction that results in an object change.

QOS_DEREG_NFY

BINARY_INTEGER

2

Purge registration on first notification

QOS_ROWIDS

BINARY_INTEGER

4

Require rowids of modified rows



Operational Notes

Troubleshooting

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

See Also:

For more information about troubleshooting Database Change Notification, see Oracle Database Application Developer's Guide - Fundamentals.

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.

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

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

Table 23-2 SYS.CHNF$_DESC Object Type

Attribute Description

registration_id

Registration ID returned during registration

transaction_id

Transaction ID. transaction_id of the transaction that made the change. Will be NULL unless the event_type is EVENT_OBJCHANGE

dbname

Name of database

event_type

Database event associated with the notification. Can be one of EVENT_OBJCHANGE (change to a registered object), EVENT_STARTUP, EVENT_SHUTDOWN or EVENT_DEREG (registration has been removed due to a timeout or other reason)

numtables

Number of modified tables. Will be NULL unless the event_type is EVENT_OBJCHANGE.

table_desc_array

Array of table descriptors. Will be NULL unless the event_type is EVENT_OBJCHANGE.



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

Table 23-3 TYPE SYS.CHNF$_TDESC Object Type

Attribute Description

opflags

Table level operation flags. This is a flag field (bit-vector) which describes the operations that occurred on the table. It can be an OR of the following bit fields - INSERTOP, UPDATEOP, DELETEOP, DROPOP, ALTEROP, ALL_ROWS. If the ALL_ROWS (0x1) bit is set it means that either the entire table is modified (for example, DELETE * FROM t) or row level granularity of information is not requested or not available in the notification and the receiver has to conservatively assume that the entire table has been invalidated.

table_name

Name of modified table

numrows

Number of modified rows within the table. numrows will be NULL and hence should not be accessed if the ALL_ROWS bit is set in the table change descriptor.

row_desc_array

Array of row descriptors. This field will be NULL if the ALL_ROWS bit is set in opflags.



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

Table 23-4 TYPE SYS.CHNF$_RDESC Object Type

Attribute Description

opflags

Row level operation flags. The flag field (bit vector) describes the operations in the row (could be INSERTOP, UPDATEOP or DELETEOP).

row_id

The rowid of the modified row



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

Table 23-5 TYPE SYS.CHNF$_REG_INFO Object Type

Attribute Description

callback

Name of the server side PL/SQL procedure to be executed on a notification. Prototype is <call_backname>(ntfnds IN SYS.chnf$_desc)

qosflags

Quality of service flags. Can be set to an OR of QOS_RELIABLE / QOS_DEREG_NFY / QOS_ROWIDS:

  • 0x1 (QOS_RELIABLE): Notifications are reliable (persistent) and survive instance death. This means that on an instance death in a RAC cluster, surviving instances will be able to deliver any queued invalidations. Similarly, pending invalidations can be delivered on instance restart, in a single instance configuration. The disadvantage is that there is a CPU cost/ latency involved in inserting the invalidation message to a persistent store. If this parameter is false, then server side CPU and latency are minimized, because invalidations are buffered into an in memory queue but the client could lose invalidation messages on an instance shutdown.

  • 0x2 (QOS_DEREG_NFY): The registration will be expunged on the first notification

  • 0x4 (QOS_ROWIDS): The notification needs to include information about the rowids that were modified

timeout

If set to a non-zero value, specifies the time in seconds after which the registration is automatically expunged by the database. If zero / NULL, the registration lives until explicitly deregistered. Note that the timeout option can be combined with the purge on notification (QOS_DEREG_NFY) option as well.

operations_filter

if non-zero, specifies a filter to be selectively notified on certain operations. These flags can be used to filter based on specific operation types:

  • 0: Notify on all operations (DBMS_CHANGE_NOTIFICATION.ALL_OPERATIONS)

  • 0x2: Notify on every INSERT (DBMS_CHANGE_NOTIFICATION.INSERTOP)

  • 0x4: Notify on every UPDATE (DBMS_CHANGE_NOTIFICATION.UPDATEOP)

  • 0x8: Notify on every DELETE (DBMS_CHANGE_NOTIFICATION.DELETEOP)

A combination of operations can be specified by using a bitwise OR.

transaction_lag

Lag between consecutive notifications in units of transactions. Can be used to specify the number of transactions/database changes, by which the client is willing to lag behind the database. If 0, it means that the client needs to receive an invalidation message as soon as it is generated


Usage Notes


Summary of DBMS_CHANGE_NOTIFICATION Subprograms

Table 23-6 DBMS_CHANGE_NOTIFICATION Package Subprograms

Subprogram Description

DEREGISTER Procedure

De-subscribes the client with the supplied registration identifier (ID)

ENABLE_REG Procedure

Begins a registration block using an existing registration identifier (ID)

NEW_REG_START Function

Begins a new registration block

REG_END Procedure

Ends the registration boundary



DEREGISTER Procedure

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

Syntax

DBMS_CHANGE_NOTIFICATION.DEREGISTER (
  regid IN NUMBER);

Parameters

Table 23-7 DEREGISTER Procedure Parameters

Parameter Description

regid

Client registration ID


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

Table 23-8 ENABLE_REG Procedure Parameters

Parameter Description

regid

Client registration ID


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

Table 23-9 NEW_REG_START Function Parameters

Parameter Description

sys.chnf$_reg_info

Registration descriptor describing the notification handler and other properties of the registration


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


REG_END Procedure

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

Syntax

DBMS_CHANGE_NOTIFICATION.REG_END;