This discussion is archived
2 Replies Latest reply: Apr 3, 2013 2:43 AM by ErikTrip-DarwinIT RSS

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

ErikTrip-DarwinIT Explorer
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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
    ErikTrip-DarwinIT Explorer
    Currently Being Moderated
    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

Legend

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