3 Replies Latest reply: Nov 21, 2012 8:08 AM by WGabriel RSS

    Dequeue from an exception queue

    975406
      Hi,

      I am very new to oracle AQ and trying dequeuing of message from an exception queue. I have created a procedure for dequeuing a message from exception queue and is working fine. I have to execute this procedure manually each time a message is moved to the exception queue. Is there any mechanism to call this procedure automatically each time the message is moved to exception queue? Please help.

      Thanks in Advance
      Lokesh
        • 1. Re: Dequeue from an exception queue
          WGabriel
          Hello,

          if you are familiar with database jobs ( e.g. DBMS_JOB or DBMS_SCHEDULER )
          you could setup a procedure to listen at your exception queue ( endless loop with DBMS_AQ.DEQUEUE ).
          Every time a row will be inserted you could dequeue this row automatically.

          Kind regards,

          WoG
          • 2. Re: Dequeue from an exception queue
            975406
            Thanks WoG.

            I am not familiar with database jobs :(
            Could you please provide a simple example to set up a listener at exception queue?

            Thanks & Regards,
            Lokesh M
            • 3. Re: Dequeue from an exception queue
              WGabriel
              Hello,

              as you wrote you already have a code snippet with DBMS_AQ.DEQUEUE of an exception queue.
              Wrap this DEQUEUE in an infinite loop (LOOP ... END LOOP). In order to have this procedure as an autonomous session
              you can start it as a database job:
              DECLARE 
              
                 v_jobno NUMBER(6); 
              
              BEGIN
                  DBMS_JOB.SUBMIT( v_jobno, 
                                  'BEGIN <your module>; END;',
                                  SYSDATE, NULL );
                  COMMIT;
              END;
              /
              Kind regards,

              WoG