CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_CHANGE_NOTIFICATION

Version 11.1
 
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
 
Other 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  WRITE_NF
DBA_CHANGE_NOTIFICATION_REGS X$KTCNREG
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
 
CQ_NOTIFICATION_QUERYID (new 11g)

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.qus_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  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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [264 users online]    © 2010 psoug.org