This discussion is archived
3 Replies Latest reply: Jul 3, 2013 6:09 AM by Greg.Spall RSS

Re enable dequeueing for expired messages in AQ

929255 Newbie
Currently Being Moderated

This is copied from SQL and PL/SQLDiscussions

I tried to perform 'Move' but I must be doing sth wrong.

 

Dear all,

 

I need to reprocess expired messages in an advanced queue.

Is there a script that allows either to retry dequeuing or to re insert/enqueue again the messages?

 

The creation script of the queue follows:

  1. BEGIN 
  2.   SYS.DBMS_AQADM.CREATE_QUEUE 
  3.   ( 
  4.     QUEUE_NAME          =>   'CC.PMTS' 
  5.    ,QUEUE_TABLE         =>   'CC.PMTS_DATA' 
  6.    ,QUEUE_TYPE          =>   SYS.DBMS_AQADM.NORMAL_QUEUE 
  7.    ,MAX_RETRIES         =>   0 
  8.    ,RETRY_DELAY         =>   0 
  9.    ,RETENTION_TIME      =>   0 
  10.    ,COMMENT             =>   'Comment' 
  11.    ); 
  12. END; 

Thanks

  • 1. Re: Re enable dequeueing for expired messages in AQ
    Greg.Spall Expert
    Currently Being Moderated

    Well, yes, that's what the MAX_RETRIES and RETRY_DELAY does:

     

    BEGIN 
      SYS.DBMS_AQADM.CREATE_QUEUE 
      ( 
        QUEUE_NAME          =>   'CC.PMTS' 
       ,QUEUE_TABLE         =>   'CC.PMTS_DATA' 
       ,QUEUE_TYPE          =>   SYS.DBMS_AQADM.NORMAL_QUEUE 
       ,MAX_RETRIES         =>   6 
       ,RETRY_DELAY         =>   10 
       ,RETENTION_TIME      =>   0 
       ,COMMENT             =>   'Comment' 
       ); 
    END; 

     

    There, that will retry the item 6 times, with a 10s delay between each retry. (Note: I haven't verified the time, but if memory serves correctly, it is by seconds)

  • 2. Re: Re enable dequeueing for expired messages in AQ
    929255 Newbie
    Currently Being Moderated

    My problem is that the messages are expired and I want to reprocess them. Not how to configure the queue.

    Thank you

  • 3. Re: Re enable dequeueing for expired messages in AQ
    Greg.Spall Expert
    Currently Being Moderated

    929255 wrote:

     

    My problem is that the messages are expired and I want to reprocess them. Not how to configure the queue.

    Thank you

     

    Oh, then just go fetch it from the expired queue, and re-queue it. I don't think there's a given script just :

     

    1) retrieve record/item from expired queue.

    2) re-queue it to your main queue.

Legend

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