Change Notification PL/SQL Example #2

Permissions

As SYS:
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. 

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

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.

3. Run DML + commit.
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;
/

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

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

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';