Registered a sign on the notification.

DECLARE REGDS SYS.CHNF$_REG_INFO; regid NUMBER; nId NUMBER; qosflags NUMBER; BEGIN qosflags:= DBMS_CQ_NOTIFICATION.QOS_RELIABLE + DBMS_CQ_NOTIFICATION.QOS_ROWIDS; REGDS := SYS.CHNF$_REG_INFO ('CALLBACK_PRC', qosflags, 0,dbms_cq_notification.updateop,0); regid := DBMS_CQ_NOTIFICATION.NEW_REG_START (REGDS); SELECT id INTO nid FROM TEST WHERE ROWNUM=1; DBMS_CQ_NOTIFICATION.REG_END; END;

Created CALLBACK procedure "CALLBACK_PRC", which in turn inserts data into a specific table. I ran into the problem of updating a large number of rows in a single transaction. those. perform:

 DECLARE BEGIN FOR rec IN (SELECT sv FROM TAB1 WHERE ROWNUM<=2000) LOOP UPDATE TEST SET STATUS =1 WHERE ID = rec.sv; END LOOP; COMMIT; END;

Notification does not work. CALLBACK_PRC does not even enter. I execute commit within the limits of each change, the notification works:

 DECLARE BEGIN FOR rec IN (SELECT sv FROM TAB1 WHERE ROWNUM<=2000) LOOP UPDATE TEST SET STATUS =1 WHERE ID = rec.sv; COMMIT; END LOOP; COMMIT; END;

Tell me, maybe there are some add-ons of Oraklovye, which would help solve the problem with the update notification of a large number of rows in a single transaction.

  • Moreover, when performing DECLARE BEGIN FOR rec IN (SELECT sv FROM TAB1 WHERE ROWNUM <= 100) LOOP UPDATE TEST SET STATUS = 1 WHERE ID = rec.sv; END LOOP; COMMIT; END; Notification works! - User_1991

1 answer 1

Judging by the documentation, everything is fair:

DML or DDL transaction commits .

  • Everything is clear, only DECLARE BEGIN FOR rec IN (SELECT sv FROM TAB1 WHERE ROWNUM <= 100) LOOP UPDATE TEST SET STATUS = 1 WHERE ID = rec.sv; END LOOP; COMMIT; END; - works, and more than 100 (for example, 101) - no longer. - User_1991
  • @ User_1991, works before commit; ? - MaxU