8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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...