3 Replies Latest reply: Jan 14, 2013 7:15 AM by 758358 RSS

    Oracle AQ and Change Notification

    984488
      Hello everyone,

      I want to keep track of insertions into a table and put the new rows into a message in a queue. I made a trigger that enqueues the message and works pretty well.
      But now i found Oracle Change Notification and i thought i can use this feature instead of my trigger. I followed the instructions from here and managed to insert some data in nfrowchanges table (Example 13-4 Procedure).

      My problem is that if i add in the procedure the code for enqueuing the message (instead of that insert) it seems that it is not executed and i do not understand why.
      Any hint would help me a lot because i don't know what to do next.

      Thanks in advance.
        • 1. Re: Oracle AQ and Change Notification
          damorgan
          No version number and no code so I have no idea what you are doing ... or doing incorrectly. But I can tell you that the demos on my website work (at least they do in all fully supported versions of the product).

          http://www.morganslibrary.html/library.html

          Look up "DBMS_CHANGE_NOTIFICATION"
          • 2. Re: Oracle AQ and Change Notification
            984488
            Hello
            I use version 11.2.0.1. As mentioned above i use the following procedure:
            /* give all necessary grants, create the queue and start it - no need to put this here */
            
            Declare
              v_cn_recip       SYS.CHNF$_REG_INFO;
              v_regid          Number;
              v_empno         scott.emp.empno%TYPE;
            BEGIN
              v_cn_recip := Sys.Chnf$_Reg_Info('quser.emp_callback', 
                            DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, 0, 2, 0);
              -- begin the registration boundary
              v_regid := Dbms_Change_Notification.New_Reg_Start(v_cn_recip);
                Select empno 
                  Into   v_empno
                From   scott.emp
                Where  ROWNUM < 2;
              -- end the registration boundary
              Dbms_Change_Notification.Reg_End;
              DBMS_OUTPUT.PUT_LINE('the registration id for this query is '||v_regid);
            END;
            /
            
            --database table to hold the record of notification events received
            create table emprowchanges(
                   regid number, 
                   table_name Varchar2(100), 
                   row_id Varchar2(2000));
                   
            Create Or Replace Procedure quser.emp_callback(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);
            message VARCHAR2(128);
             
            BEGIN
              regid := ntfnds.registration_id;
              numtables := ntfnds.numtables;
              event_type := ntfnds.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;
                  numrows := ntfnds.table_desc_array(i).numrows;
                 
                  FOR j IN 1..numrows LOOP
                      row_Id := Ntfnds.Table_Desc_Array(I).Row_Desc_Array(J).Row_Id;
                      insert into emprowchanges values(regid, tbname, row_id);
                      select to_char(empno) || '$' || ename into message from scott.emp where rowid=row_id; -- construct the message 
                      
                      -- now, the problem
                      -- if i comment this, the procedure makes the insert above; otherwise no insert and also no message in queue
                      enqueue(message);
                  END LOOP;
                  
                END LOOP;
              END IF;
              commit;
            END;
            /
            
            create or replace
            PROCEDURE enqueue (payload VARCHAR2) 
            IS
                 queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
                 message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
                 message_id RAW(16);
                 agent SYS.AQ$_AGENT := SYS.AQ$_AGENT(' ', null, 0);
                 my_message SYS.AQ$_JMS_TEXT_MESSAGE;
            begin
                 my_message := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
                 my_message.set_text(payload);
             
                 DBMS_AQ.ENQUEUE(
                      queue_name => 'myqueue',
                      enqueue_options => queue_options,
                      message_properties => message_properties,
                      payload => my_message,
                      msgid => message_id
                 );
                 
                 commit;
            end;
            /
            • 3. Re: Oracle AQ and Change Notification
              758358
              Hi,

              The procedure call is probably failing - did you check the database alert logs and trace files for evidence of errors when the enqueue procedure is active?

              If you can't find anything try adding some instrumentation to your code to trace its position.

              Thanks
              Paul