Oracle DBMS_CHANGE_NOTIFICATION
Version 11.2.0.3
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmschnf.sql
First Available 10.2

In 11gR1 the synonym DBMS_CQ_NOTIFICATIONS was added for this package
Constants
Name Data Type Value Description
 Published To Database Types
EVENT_NONE BINARY_INTEGER 0 No event
EVENT_STARTUP BINARY_INTEGER 1 Instance startup notification
EVENT_SHUTDOWN BINARY_INTEGER 2 Instance shutdown notification
EVENT_SHUTDOWN_ANY BINARY_INTEGER 3 Any instance shutdown when running RAC
EVENT_DROP_DB BINARY_INTEGER 4 Database has been dropped
EVENT_DEREG BINARY_INTEGER 5 Registration has been removed
EVENT_OBJCHANGE BINARY_INTEGER 6 Registration has been removed
EVENT_QUERYCHANGE BINARY_INTEGER 7  
 
 Registration Quality of Service Properties
QOS_RELIABLE BINARY_INTEGER 1 Reliable or persistent notification. Also implies that the notifications will be inserted into the persistent storage atomically with the committing transaction that results in an object change.
QOS_DEREG_NFY BINARY_INTEGER 2 Purge registration on first notification
QOS_ROWIDS BINARY_INTEGER 4 Require rowids of modified rows
QOS_QUERY BINARY_INTEGER 8  
QOS_BEST_EFFORT BINARY_INTEGER 16  
 
 Table Operations
INSERTOP BINARY_INTEGER 2 Insert record
UPDATEOP BINARY_INTEGER 4 Update record
DELETEOP BINARY_INTEGER 8 Delete record
ALTEROP BINARY_INTEGER 16 Table altered
DROPOP BINARY_INTEGER 32 Table dropped
 
 Miscellaneous Constants
ALL_OPERATIONS BINARY_INTEGER 0 All rows within the table may have been potentially modified by any operation
ALL_ROWS BINARY_INTEGER 1 All rows within the table may have been potentially modified
UNKNOWNOP BINARY_INTEGER 64 Unknown operation

Other Constants
Name Data Type Value
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
 Notification Grouping Class
NTFN_GROUPING_CLASS_TIME BINARY_INTEGER 1
 Notification Grouping Type
NTFN_GROUPING_TYPE_SUMMARY BINARY_INTEGER 1
NTFN_GROUPING_TYPE_LAST BINARY_INTEGER 2
Notification Grouping Repeat Count
NTFN_GROUPING_FOREVER BINARY_INTEGER -1
Dependencies
AQ$_REG_INFO DBMS_CHNF_LIB
AQ$_REG_INFO_LIST DBMS_CQ_NOTIFICATION
CHNF$_REG_INFO DBMS_RLMGR_IR
CHNF$_REG_INFO_OC4J USER_CHANGE_NOTIFICATION_REGS
DBMS_AQ X$KTCNREG
DBA_CHANGE_NOTIFICATION_REGS  
Initialization Parameter dml_locks must be non-zero
Object 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);
Security Model No privileges are granted. Runs under AUTHID CURRENT_USER
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);
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);

Note: This may produce the following error:

SQL> 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 / as sysdba

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

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
 
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
 
SET_ROWID_THRESHOLD
Undocumented dbms_change_notification.set_rowid_threshold(
tbname    IN VARCHAR2,
threshold IN NUMBER);
See ENABLE_REG Demo
 
Related Topics
AUDITING
DBMS_FGA
DBMS_SERVER_ALERT
DBMS_WLM
 
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-2013 Daniel A. Morgan All Rights Reserved