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 row_ids to client side listener using
-- UTL_HTTP
END LOOP;
END LOOP;
END IF;
COMMIT;
END chnf_callback;
/
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;