2 Replies Latest reply: Apr 3, 2013 4:43 AM by Erik Trip - Darwin IT RSS

    how to relate rows from AQ$AQ_SRVNTFN_TABLE_1 to the application queueu

    Erik Trip - Darwin IT
      Hello
      For some reason (I do not know why) I have a lot of rows in the notification exception queue. The reason is max_retry_exceeded.

      I have an application queue with a plsql callback procedure

      When I query rows from AQ$AQ_SRVNTFN_TABLE_1 I see in the user_data column these kind of values:


      SYS.AQ$_SRVNTFN_MESSAGE('"TWQB7"."TWQ_IFKP_QUEUE_V2"','TWQ_IFKP_QUEUE_V2_SUBSCRIBER','[B@10048abc',1,0,-1,NULL,0,NULL,'2013-03-26 16:28:21.0',0,NULL,NULL,0,NULL,'"TWQB7"."TWQ_IFKP_QUEUE_V2":"TWQ_IFKP_QUEUE_V2_SUBSCRIBER"',1,'TWQB7.P_WF_IFKP_AQ.TWQ_QUEUE_CALLBACK_PROCEDURE','[B@1a3044db',50,NULL,0,NULL,1,NULL,0,1,0,NULL,NULL,NULL,1201)

      How can I relate this back to my application queue?

      Regards Erik                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
        • 1. Re: how to relate rows from AQ$AQ_SRVNTFN_TABLE_1 to the application queueu
          sushaant
          Yes there is max retry parameter allocated to the normal queues present in under the queue table. You can get that value of respective queue from user_queues table. Apart from that please use below snippet to dequeue payload from oracle exception queue.

          declare
          l_key_msgid RAW(16);
          l_q_payload "Provide DataType of the Queue Payload";
          l_deq_queueopts DBMS_AQ.DEQUEUE_OPTIONS_T;
          l_deq_msgprops DBMS_AQ.MESSAGE_PROPERTIES_T;
          l_deq_msgid RAW(16);
          l_en_queueopts DBMS_AQ.ENQUEUE_OPTIONS_T;
          l_en_msgprops DBMS_AQ.MESSAGE_PROPERTIES_T;
          l_en_msgid RAW(16);

          begin

          l_key_msgid := 'Message id of the record';

          l_q_payload := null;
          l_deq_queueopts.visibility := DBMS_AQ.ON_COMMIT;
          l_deq_queueopts.wait := DBMS_AQ.NO_WAIT;
          l_deq_queueopts.navigation := DBMS_AQ.FIRST_MESSAGE;
          l_deq_queueopts.dequeue_mode := DBMS_AQ.REMOVE;
          l_deq_queueopts.msgid := l_key_msgid;
          l_en_queueopts.visibility := DBMS_AQ.ON_COMMIT;

          dbms_aq.dequeue ('ptovide exception queue name',
          l_deq_queueopts,
          l_deq_msgprops,
          l_q_payload,
          l_deq_msgid);

          dbms_aq.enqueue (l_q_payload.destination_q,
          l_en_queueopts,
          l_en_msgprops,
          l_q_payload,
          l_en_msgid);

          commit;

          end;


          Cheers
          Sush
          • 2. Re: how to relate rows from AQ$AQ_SRVNTFN_TABLE_1 to the application queueu
            Erik Trip - Darwin IT
            Hello
            I know that there is a max retry parameter.
            What I want to know is the relation between the row the the notification exception queue and the original row in the application queue so that I can decide if I have to enqueue these messages again.

            Regards Erik