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);
Dependencies
AQ$_REG_INFO CHNF$_REG_INFO_OC4J DBMS_CHNF_LIB
AQ$_REG_INFO_LIST DBMS_AQ DBMS_CQ_NOTIFICATION
CHNF$_REG_INFO    
Documented Yes: Packages and Types Reference
First Available 10.2
Initialization Parameter dml_locks must be non-zero
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmschnf.sql
Subprograms
CQ_NOTIFICATION_QUERYID NEW_REG_START REG_END
DEREGISTER NEW_REG_START_OC4J SET_ROWID_THRESHOLD
ENABLE_REG    
 
CQ_NOTIFICATION_QUERYID
Undocumented dbms_change_notification.cq_notification_queryid RETURN NUMBER;
SELECT dbms_change_notification.cq_notification_queryid
FROM dual;
 
DEREGISTER
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

-- but yet it still works.

SELECT *
FROM dba_change_notification_regs;
 
ENABLE_REG
Adds objects to an existing registration identifier dbms_change_notification.enable_reg(regid IN NUMBER);
conn sys as sysdba@pdbdev

GRANT execute ON dbms_change_notification TO uwclass;
GRANT change notification TO uwclass;

col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%job%';

-- ALTER SYSTEM SET job_queue_processes = 10 SCOPE=BOTH;

conn uwclass/uwclass@pdbdev

CREATE TABLE nfevents(
regid NUMBER,
event_type NUMBER);

CREATE TABLE nftablechanges(
regid NUMBER,
table_name VARCHAR2(100),
table_operation number);

CREATE TABLE nfrowchanges(
regid NUMBER,
table_name VARCHAR2(100),
row_id VARCHAR2(30));

-- create change handler

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;
/

CREATE TABLE chngnote AS
SELECT * FROM servers;

desc chngnote

SELECT *
FROM chngnote;

set serveroutput on

-- register an event
DECLARE
 dept_id  NUMBER;
 qosflags NUMBER;
 regds    SYS.CHNF$_REG_INFO;
 regid    NUMBER;
 sid      NUMBER;
BEGIN
  qosflags := dbms_change_notification.qos_reliable + dbms_change_notification.qos_rowids;

  dbms_output.put_line(TO_CHAR(qosflags));

  regds := SYS.CHNF$_REG_INFO('chnf_callback', qosflags, 0,0,0);

  dbms_output.put_line(regds.callback);
  dbms_output.put_line(regds.qosflags);
  dbms_output.put_line(regds.timeout);
  dbms_output.put_line(regds.operations_filter);
  dbms_output.put_line(regds.transaction_lag);

  regid := dbms_change_notification.new_reg_start(regds);

  dbms_output.put_line(regid);

  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;

SELECT * FROM nfevents;

SELECT * FROM nftablechanges;

SELECT * FROM nfrowchanges;

exec dbms_change_notification.deregister(27);

SELECT * FROM dba_change_notification_regs;
 
NEW_REG_START
Begin a new registration block dbms_change_notification.new_reg_start(regds IN sys.chnf$_reg_info)
RETURN NUMBER;
See ENABLE_REG Demo Above
 
NEW_REG_START_OC4J
Undocumented dbms_change_notification.new_reg_start_oc4j(regds IN sys.chnf$_reg_info_oc4j)
RETURN NUMBER;
TBD
 
REG_END
End a registration block dbms_change_notification.reg_end;
See ENABLE_REG Demo Above
 
SET_ROWID_THRESHOLD
Undocumented dbms_change_notification.set_rowid_threshold(
tbname    IN VARCHAR2,
threshold IN NUMBER);
See ENABLE_REG Demo Above

Related Topics
AUDITING
Built-in Functions
Built-in Packages
DBMS_FGA
DBMS_SERVER_ALERT
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx