Permissions
As SYS:
GRANT EXECUTE ON SYS.DBMS_CHANGE_NOTIFICATION TO XXX_YYYYY_ZZZZZ;
GRANT EXECUTE ON SYS.DBMS_CHANGE_NOTIFICATION TO XXX_YYYYY_ZZZZZ;
GRANT CHANGE NOTIFICATION TO XXX_YYYYY_ZZZZZ;
Objects to test Notification from PL/SQL:
CREATE TABLE NOTIFICATIONS (
id NUMBER,
message VARCHAR2(1000),
notification_date DATE )
TABLESPACE IGT_TABLE;
CREATE SEQUENCE notifications_seq NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE;
Firewall
Applicable if application is running on another server.
Applicable if application is running on another server.
Once registration was done, there should be an entry in DBA_CHANGE_NOTIFICATION_REGS
SELECT callback FROM DBA_CHANGE_NOTIFICATION_REGS
The result should be something like:
net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=47632))?PR=0
net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=47633))?PR=0
Each JVM would have is own port it is listening to.
Port numbers by default start at 47632, and incremented by 1
Each JVM would have is own port it is listening to.
Port numbers by default start at 47632, and incremented by 1
Ports 47632,47633, etc should be opened in Firewall.
From Oracle IP to the JVM IP (in this example, to IP 10.20.30.40)
Flow
1. Create Callback Procedure
2. Create registration between Callback Procedure and SQL that reflect a change.
1. Create Callback Procedure
2. Create registration between Callback Procedure and SQL that reflect a change.
3. Run DML + commit.
4. Check Notification took place, by checking Oracle DBA_CHANGE_NOTIFICATION_REGS and Application table (NOTIFICATIONS).
4. Check Notification took place, by checking Oracle DBA_CHANGE_NOTIFICATION_REGS and Application table (NOTIFICATIONS).
Callback Procedure
CREATE OR REPLACE PROCEDURE callback_proc (ntfnds IN SYS.CHNF$_DESC) IS
regid NUMBER;
tbname VARCHAR2(60);
event_type NUMBER;
numtables NUMBER;
operation_type NUMBER;
numrows NUMBER;
row_id VARCHAR2(2000);
l_message VARCHAR2(4000) := NULL;
BEGIN
regid := ntfnds.registration_id;
numtables := ntfnds.numtables;
event_type := ntfnds.event_type;
--INSERT INTO nfevents VALUES(regid, event_type);
l_message := 'Registration Level. regid : '||regid||', event_type: '||event_type;
INSERT INTO NOTIFICATIONS (id, message, notification_date)
VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
COMMIT;
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);
l_message := 'Table Level. regid: '||regid||', tbname : '||tbname||', operation_type: '||operation_type;
INSERT INTO NOTIFICATIONS (id, message, notification_date)
VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
COMMIT;
/* Optionally, send the table name and operation_type to a client-side listener using UTL_HTTP */
/* If interested in 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);
l_message := 'Row Level: regid: '||regid||', tbname : '||tbname||', row_id: '||row_id;
INSERT INTO NOTIFICATIONS (id, message, notification_date)
VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
COMMIT;
/* Optionally, send out ROWIDs to a client-side listener using UTL_HTTP */
END LOOP;
END LOOP;
END IF;
COMMIT;
END callback_proc;
Create the Registration with callback Procedure
DECLARE
v_reg_info SYS.CHNF$_REG_INFO;
v_regid NUMBER;
v_upd_time IPN_INVALIDATE_TRIGGER.ts_update_time%TYPE;
qosflags NUMBER;
BEGIN
-- Create registration info: callback procedure, quality of service (include ROWIDs), operations filter (UPDATE only), timeout=0, purge_on_ntfn=0
qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
v_reg_info := SYS.CHNF$_REG_INFO('XXX_YYYYY_ZZZZZ.callback_proc', qosflags, 0, 0, 0);
-- Begin registration
v_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start(v_reg_info);
-- Register the query (must return at least one row; limit to avoid multiple fetch errors)
SELECT ts_update_time INTO v_upd_time FROM IPN_INVALIDATE_TRIGGER WHERE module_name = 'SUB_API';
-- End registration
DBMS_CHANGE_NOTIFICATION.reg_end;
DBMS_OUTPUT.PUT_LINE('Registration ID for this query: ' || v_regid);
END;
/
regid NUMBER;
tbname VARCHAR2(60);
event_type NUMBER;
numtables NUMBER;
operation_type NUMBER;
numrows NUMBER;
row_id VARCHAR2(2000);
l_message VARCHAR2(4000) := NULL;
BEGIN
regid := ntfnds.registration_id;
numtables := ntfnds.numtables;
event_type := ntfnds.event_type;
--INSERT INTO nfevents VALUES(regid, event_type);
l_message := 'Registration Level. regid : '||regid||', event_type: '||event_type;
INSERT INTO NOTIFICATIONS (id, message, notification_date)
VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
COMMIT;
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);
l_message := 'Table Level. regid: '||regid||', tbname : '||tbname||', operation_type: '||operation_type;
INSERT INTO NOTIFICATIONS (id, message, notification_date)
VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
COMMIT;
/* Optionally, send the table name and operation_type to a client-side listener using UTL_HTTP */
/* If interested in 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);
l_message := 'Row Level: regid: '||regid||', tbname : '||tbname||', row_id: '||row_id;
INSERT INTO NOTIFICATIONS (id, message, notification_date)
VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
COMMIT;
/* Optionally, send out ROWIDs to a client-side listener using UTL_HTTP */
END LOOP;
END LOOP;
END IF;
COMMIT;
END callback_proc;
Create the Registration with callback Procedure
DECLARE
v_reg_info SYS.CHNF$_REG_INFO;
v_regid NUMBER;
v_upd_time IPN_INVALIDATE_TRIGGER.ts_update_time%TYPE;
qosflags NUMBER;
BEGIN
-- Create registration info: callback procedure, quality of service (include ROWIDs), operations filter (UPDATE only), timeout=0, purge_on_ntfn=0
qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
v_reg_info := SYS.CHNF$_REG_INFO('XXX_YYYYY_ZZZZZ.callback_proc', qosflags, 0, 0, 0);
-- Begin registration
v_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start(v_reg_info);
-- Register the query (must return at least one row; limit to avoid multiple fetch errors)
SELECT ts_update_time INTO v_upd_time FROM IPN_INVALIDATE_TRIGGER WHERE module_name = 'SUB_API';
-- End registration
DBMS_CHANGE_NOTIFICATION.reg_end;
DBMS_OUTPUT.PUT_LINE('Registration ID for this query: ' || v_regid);
END;
/
PL/SQL procedure successfully completed
--Make the Update
UPDATE XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER SET ts_update_time = TO_CHAR(SYSDATE,'YYYYMMSS hh24:mi:ss') WHERE module_name = 'SUB_API';
1 row updated
COMMIT;
--Check Change Notification happened
SELECT * FROM DBA_CHANGE_NOTIFICATION_REGS
USERNAME REGID REGFLAGS CALLBACK OPERATIONS_FILTER CHANGELAG TIMEOUT TABLE_NAME
--------------- ------ -------- ------------------------------------------
----------------- --------- ---------- ---------------------------------------
XXX_YYYYY_ZZZZZ 1520 13 plsql://XXX_YYYYY_ZZZZZ.callback_proc?PR=0 0 0 4294967295 XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER
1 row updated
COMMIT;
--Check Change Notification happened
SELECT * FROM DBA_CHANGE_NOTIFICATION_REGS
USERNAME REGID REGFLAGS CALLBACK OPERATIONS_FILTER CHANGELAG TIMEOUT TABLE_NAME
--------------- ------ -------- ------------------------------------------
----------------- --------- ---------- ---------------------------------------
XXX_YYYYY_ZZZZZ 1520 13 plsql://XXX_YYYYY_ZZZZZ.callback_proc?PR=0 0 0 4294967295 XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER
SELECT * FROM NOTIFICATIONS
ID MESSAGE
---------- --------------------------------------------------------------------------
NOTIFICATION_DATE
-----------------
2 Registration Level. regid : 1520, event_type: 6 20250809 16:16:28
3 Table Level. regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER,
20250809 16:16:28
4 Row Level: regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER, 20250809 16:16:28
ID MESSAGE
---------- --------------------------------------------------------------------------
NOTIFICATION_DATE
-----------------
2 Registration Level. regid : 1520, event_type: 6 20250809 16:16:28
3 Table Level. regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER,
20250809 16:16:28
4 Row Level: regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER, 20250809 16:16:28
If still not working, Debug with traces
Open trace files:
ALTER SYSTEM SET events '10867 trace name context forever, level 10';
Then:
Recreate the activity
This should generate oracle tracing
Trace file would be generated under directory 'diagnostic_dest'
SELECT * FROM V$PARAMETER WHERE name = 'diagnostic_dest'
To turn of the trace files:
ALTER SYSTEM SET EVENTS '10867 trace name context off';
