Oracle DBMS_CQ_NOTIFICATION aka DBMS_CHANGE_NOTIFICATION
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 19c
What's New In 20c-21c

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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx