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:

CREATE TABLE NOTIFICATIONS (

  id NUMBER,

  message VARCHAR2(1000),

  notification_date DATE )

TABLESPACE IGT_TABLE;

CREATE SEQUENCE notifications_seq  NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE;

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

Port 

Check for Oracle Notification Server Remote Port

Below port should be opened in firewall between the oracle server, and the application server(s)

$ORACLE_HOME/opmn/conf/ons.config

For example:
usesharedinstall=true

localport=6100

remoteport=6200   <<<<<<<< This port should be open in Firewall


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