Oracle DBMS_CQ_NOTIFICATION aka DBMS_CHANGE_NOTIFICATION
Version 21c
General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Allows the database to provide notifications, via AQ, to front-end applications written in Java and DotNet.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
Miscellaneous
ALL_OPERATIONS
BINARY_INTEGER
0
ALL_ROWS
BINARY_INTEGER
1
UNKNOWNOP
BINARY_INTEGER
64
Notification Grouping Class
NTFN_GROUPING_CLASS_TIME
BINARY_INTEGER
1
Notification Grouping Repeat Count
NTFN_GROUPING_FOREVER
BINARY_INTEGER
-1
Notification Grouping Type
NTFN_GROUPING_TYPE_SUMMARY
BINARY_INTEGER
1
NTFN_GROUPING_TYPE_LAST
BINARY_INTEGER
2
Published To Database Types
EVENT_NONE
BINARY_INTEGER
0
EVENT_STARTUP
BINARY_INTEGER
1
EVENT_SHUTDOWN
BINARY_INTEGER
2
EVENT_SHUTDOWN_ANY
BINARY_INTEGER
3
EVENT_DROP_DB
BINARY_INTEGER
4
EVENT_DEREG
BINARY_INTEGER
5
EVENT_OBJCHANGE
BINARY_INTEGER
6
EVENT_QUERYCHANGE
BINARY_INTEGER
7
Registration Quality of Service Properties
QOS_RELIABLE
BINARY_INTEGER
1
QOS_DEREG_NFY
BINARY_INTEGER
2
QOS_ROWIDS
BINARY_INTEGER
4
QOS_QUERY
BINARY_INTEGER
8
QOS_BEST_EFFORT
BINARY_INTEGER
16
String
STRING_DOMAIN_SCHEMA
BINARY_INTEGER
0
STRING_DOMAIN_DATABASE
BINARY_INTEGER
1
STRING_DOMAIN_TABLE
BINARY_INTEGER
2
STRING_TRANSITIONAL_SEARCH
BINARY_INTEGER
0
STRING_COMPLETE_SEARCH
BINARY_INTEGER
1
Table Operations
ALL_OPERATIONS
BINARY_INTEGER
0
ALL_ROWS
BINARY_INTEGER
1
INSERTOP
BINARY_INTEGER
2
UPDATEOP
BINARY_INTEGER
4
DELETEOP
BINARY_INTEGER
8
ALTEROP
BINARY_INTEGER
16
DROPOP
BINARY_INTEGER
32
UNKNOWNOP
BINARY_INTEGER
64
Data Types
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);
/
TYPE sys.chnf$_tdesc IS OBJECT OF (
opflags NUMBER,
table_name VARCHAR2(64),
numrows NUMBER,
row_desc_array CHNF$_RDESC_ARRAY);
/
TYPE sys.chnf$_tdesc_array IS VARRAY(1024) OF CHNF$_TDESC;
/
TYPE sys.chnf$_rdesc IS OBJECT OF (
opflags NUMBER,
row_id VARCHAR2(2000));
/
TYPE sys.chnf$_rdesc_array IS VARRAY(1024) OF CHNF$_RDESC;
/
TYPE sys.chnf$_reg_info IS OBJECT (
callback VARCHAR2(20),
quosflags NUMBER,
timeout NUMBER,
operations_filter NUMBER,
transaction_lag NUMBER);
De-subscribes the client with the supplied registration identifier
dbms_change_notification.deregister(regid IN NUMBER);
conn sys as sysdba@pdbdev
desc dba_change_notification_regs
set linesize 121
col username format a10
col callback format a20
col table_name format a10
SELECT *
FROM dba_change_notification_regs;
exec dbms_change_notification.deregister(21);
-- which may produce the following error:
exec dbms_change_notification.deregister(21);
BEGIN dbms_change_notification.deregister(21); END;
*
ERROR at line 1:
ORA-24950: unregister failed, registeration not found
ORA-06512: at "SYS.DBMS_CHANGE_NOTIFICATION", line 13
ORA-06512: at "SYS.DBMS_CHANGE_NOTIFICATION", line 72
ORA-06512: at line 1
CREATE OR REPLACE PROCEDURE chnf_callback(ntfnds IN SYS.CHNF$_DESC) IS
event_type NUMBER;
numtables NUMBER;
numrows NUMBER;
operation_type NUMBER;
row_id VARCHAR2(20);
regid NUMBER;
tbname VARCHAR2(60);
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 chnf_callback;
/
SELECT srvr_id INTO sid FROM chngnote WHERE rownum = 1;
dbms_change_notification.reg_end;
EXCEPTION
WHEN OTHERS THEN
dbms_change_notification.reg_end;
END;
/
SELECT * FROM dba_change_notification_regs;
SELECT * FROM nfevents;
SELECT * FROM nftablechanges;
SELECT * FROM nfrowchanges;
UPDATE chngnote
SET srvr_id = 80
WHERE srvr_id = 5;
COMMIT;
SELECT * FROM nfevents;
SELECT * FROM nftablechanges;
SELECT * FROM nfrowchanges;
ALTER TABLE chngnote RENAME COLUMN srvr_id TO srvr#;
-- event types
-- table operations
SELECT * FROM dba_change_notification_regs;
-- add another table to the same registration
DECLARE
lcode serv_inst.location_code%TYPE;
BEGIN
dbms_change_notification.enable_reg(27);
SELECT location_code INTO lcode FROM serv_inst
WHERE rownum = 1;
dbms_change_notification.reg_end;
END;
/
SELECT * FROM dba_change_notification_regs;
BEGIN
dbms_change_notification.set_rowid_threshold('SERV_INST', 3);
END;
/
UPDATE serv_inst
SET location_code = 9999
WHERE rownum = 1;
COMMIT;
SELECT * FROM nfevents;
SELECT * FROM nftablechanges;
SELECT * FROM nfrowchanges;
UPDATE serv_inst
SET location_code = 9999
WHERE rownum < 5;