This discussion is archived
3 Replies Latest reply: Jan 14, 2013 5:15 AM by 758358 RSS

Oracle AQ and Change Notification

984488 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points