Oracle DBMS_CHANGE_NOTIFICATION Change Notification

Search the Reference Library pages:

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

No event

EVENT_STARTUP

BINARY_INTEGER

Instance startup notification

EVENT_SHUTDOWN

BINARY_INTEGER

Instance shutdown notification

EVENT_SHUTDOWN_ANY

BINARY_INTEGER

Any instance shutdown when running RAC

EVENT_DROP_DB

BINARY_INTEGER

Database has been dropped

EVENT_DEREG

BINARY_INTEGER

Registration has been removed

EVENT_OBJCHANGE

BINARY_INTEGER

Registration has been removed

EVENT_QUERYCHANGE

BINARY_INTEGER

Registration Quality of Service Properties

QOS_RELIABLE

BINARY_INTEGER

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

Purge registration on first notification

QOS_ROWIDS

BINARY_INTEGER

Require rowids of modified rows

QOS_QUERY

BINARY_INTEGER

QOS_BEST_EFFORT

BINARY_INTEGER

Table Operations

INSERTOP

BINARY_INTEGER

Insert record

UPDATEOP

BINARY_INTEGER

Update record

DELETEOP

BINARY_INTEGER

Delete record

ALTEROP

BINARY_INTEGER

Table altered

DROPOP

BINARY_INTEGER

Table dropped

Other Constants

ALL_OPERATIONS

BINARY_INTEGER

All rows within the table may have been potentially modified by any operation

ALL_ROWS

BINARY_INTEGER

All rows within the table may have been potentially modified

UNKNOWNOP

BINARY_INTEGER

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

-----