DBMS_CHANGE_NOTIFICATION

Database change notification allows the objects referenced by specific queries to be associated with a callback handler procedure. The callback handler acts like an asynchronous trigger, allowing operations on the objects of interest to initiate client specific processing. Oracle suggest this functionality might be of use in multi-tier applications, where semi-static data is cached in the middle tier. In such a setup, modification of the base data could trigger the middle tier to refresh its cached data.

Management of notification registrations is done using the DBMS_CHANGE_NOTIFICATION package, which contains numerous constants to describe specific types of events and operations. In addition, a number of database types are used in conjunction with the package. Examples of their usage are shown below, but for a more detailed explanation see the links at the bottom of the article.

To use the change notification functionality, the JOB_QUEUE_PROCESSES initialization parameter must be set to a none-zero value. In addition, the user registering the queries must be granted the CHANGE NOTIFICATION privilege and have execute permission on the DBMS_CHANGE_NOTIFICATION package. The code below sets initialization parameter and creates a test user with the necessary privileges for the example in this article.

CONNECT sys/password AS SYSDBA;

ALTER SYSTEM SET job_queue_processes=1;

CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE TO test;
GRANT CHANGE NOTIFICATION TO test;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO test;

Next, we create and populate two tables (T1 and T2) as a test schema.

CONN test/test

CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

INSERT INTO t1 (id, description) VALUES (1, 'One');
INSERT INTO t1 (id, description) VALUES (2, 'Two');
COMMIT;

CREATE TABLE t2 (
  id           NUMBER,
  t1_id        NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT t2_pk PRIMARY KEY (id),
  CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1(id)
);

INSERT INTO t2 (id, t1_id, description) VALUES (1, 1, 'One - One');
INSERT INTO t2 (id, t1_id, description) VALUES (2, 1, 'Two - One');
INSERT INTO t2 (id, t1_id, description) VALUES (3, 2, 'Three - Two');
INSERT INTO t2 (id, t1_id, description) VALUES (4, 2, 'Four - Two');
COMMIT;

For the purposes of this test, the notifications will be logged in the table defined below.

CREATE TABLE notifications (
  id                 NUMBER,
  message            VARCHAR2(4000),
  notification_date  DATE
);

CREATE SEQUENCE notifications_seq;

With the schema in place, we must define a procedure as a callback handler, that does the actual work in the event of a notification taking place. The following procedure inserts notification records describing the object and operation on which an "Object Change" event occurred.

CREATE OR REPLACE PROCEDURE tables_changed_chnt(ntfnds IN SYS.chnf$_desc) IS
  l_regid           NUMBER;
  l_table_name      VARCHAR2(60);
  l_event_type      NUMBER;
  l_numtables       NUMBER;
  l_operation_type  NUMBER;
  l_numrows         NUMBER;
  l_row_id          VARCHAR2(20);
  l_operation       VARCHAR2(20);
  l_message         VARCHAR2(4000) := NULL;
BEGIN
  l_regid      := ntfnds.registration_id;
  l_numtables  := ntfnds.numtables;
  l_event_type := ntfnds.event_type;

  IF l_event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE THEN
    FOR i IN 1 .. l_numtables LOOP
      l_table_name      := ntfnds.table_desc_array(i).table_name;
      l_operation_type  := ntfnds.table_desc_array(i).Opflags;
 
      IF (BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
        l_numrows := ntfnds.table_desc_array(i).numrows;
      ELSE 
        l_numrows :=0;  /* ROWID INFO NOT AVAILABLE */
      END IF;
     
      CASE
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.INSERTOP) != 0 THEN
          l_operation := 'Records Inserted';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.UPDATEOP) != 0 THEN
          l_operation := 'Records Updated';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.DELETEOP) != 0 THEN
          l_operation := 'Records Deleted';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.ALTEROP) != 0 THEN
          l_operation := 'Table Altered';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.DROPOP) != 0 THEN
          l_operation := 'Table Dropped';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.UNKNOWNOP) != 0 THEN
          l_operation := 'Unknown Operation';
        ELSE
          l_operation := '?';
      END CASE;
      
      l_message := 'Table (' || l_table_name || ') - ' || l_operation || '. Rows=' || l_numrows;
      
      INSERT INTO notifications (id, message, notification_date)
      VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
      COMMIT;
    END LOOP; 
  END IF;      
END;
/

Next, we register the callback handler with one or more queries. The callback handler is registered to all objects referenced by the queries between the NEW_REG_START and REG_END procedure calls.

DECLARE
  l_regds     SYS.CHNF$_REG_INFO;
  l_regid     NUMBER;
  l_qosflags  NUMBER;
  l_id        t1.id%TYPE;
BEGIN
  l_qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE  +
                DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
  l_regds := SYS.CHNF$_REG_INFO ('tables_changed_chnt', l_qosflags, 0,0,0);
  l_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start (l_regds);
  SELECT t1.id
  INTO   l_id
  FROM   t1,
         t2
  WHERE  t2.t1_id = t1.id
  AND    ROWNUM = 1;
  DBMS_CHANGE_NOTIFICATION.reg_end;
END;
/

The registration information of the callback handler with the objects can be displayed using the USER_CHANGE_NOTIFICATION_REGS view.

COLUMN table_name FORMAT A20
COLUMN callback   FORMAT A40

SELECT regid,
       table_name,
       callback
FROM   user_change_notification_regs;

     REGID TABLE_NAME           CALLBACK
---------- -------------------- ----------------------------------------
        61 TEST.T1              plsql://tables_changed_chnt?PR=0
        61 TEST.T2              plsql://tables_changed_chnt?PR=0

2 rows selected.

SQL>

Finally, we can test the notification mechanism by performing some operations on the test tables. Notice the commits between DML statements. Without these commits the notifications would be combined into a single unknown operation on multiple rows.

TRUNCATE TABLE notifications;

ALTER TABLE t1 MODIFY (description VARCHAR2(50));
ALTER TABLE t2 MODIFY (description VARCHAR2(50));
INSERT INTO t1 (id, description) VALUES (3, '3');
COMMIT;
UPDATE t1 SET description = 'Three' WHERE id = 3;
COMMIT;
DELETE FROM t1 WHERE id = 3;
COMMIT;

-- Wait a couple of seconds.

COLUMN message FORMAT A50
SELECT *
FROM   notifications
ORDER BY id;

        ID MESSAGE                                            NOTIFICATION_DATE
---------- -------------------------------------------------- --------------------
         1 Table (TEST.T1) - Table Altered. Rows=0            19-JAN-2007 15:07:43
         2 Table (TEST.T2) - Table Altered. Rows=0            19-JAN-2007 15:07:43
         3 Table (TEST.T2) - Unknown Operation. Rows=0        19-JAN-2007 15:07:43
         4 Table (TEST.T1) - Records Inserted. Rows=1         19-JAN-2007 15:07:43
         5 Table (TEST.T1) - Records Updated. Rows=1          19-JAN-2007 15:07:43
         6 Table (TEST.T1) - Records Deleted. Rows=1          19-JAN-2007 15:07:43

6 rows selected.

SQL>

The notifications are processed asynchronously, so it may take a few seconds for them to appear.

Registrations of notifications can be removed using the DEREGISTER procedure.

EXEC DBMS_CHANGE_NOTIFICATION.deregister (regid => 61);

SELECT regid,
       table_name,
       callback
FROM   user_change_notification_regs;

no rows selected

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.