This discussion is archived
1 Reply Latest reply: Jun 3, 2013 10:52 PM by davidp 2 RSS

Remove expired messages from queue automatically

1007264 Newbie
Currently Being Moderated
My application is using the active queue to send messages among different components and the messages in the queue have a validity of 30 seconds (the time to live is set to 30 so they will expire after 30 seconds).
Once these messages are expired we are not interested in processing them anymore.
Is there a way to delete the expired messages automatically without putting them in the exception queue?

I also tried to set up a scheduled job to purge the queue using the PURGE_QUEUE_TABLE. It works fine if the purge condition is NULL, but if I set it to be
purge_condition => 'msg_state = ''EXPIRED'''
then I get the following exception:

Error report:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 14:
PLS-00302: component 'DBMS_AQ_INV' must be declared
ORA-06550: line 1, column 10:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_AQADM", line 1471
ORA-06512: at line 5
00604. 00000 - "error occurred at recursive SQL level %s"

Any ideas on what is wrong with the purge condition or alternatively a better way to delete the expired messages?

  • 1. Re: Remove expired messages from queue automatically
    davidp 2 Pro
    Currently Being Moderated
    Exipred messages will go into the exception queue. When I was working with a similar AQ system, I had a scheduled job to remove the expired jobs, simply by dequeueing them from the exception queue using the normal DBMS_AQ.DEQUEUE or DBMS_AQ.DEQUEUE_ARRAY.

    I've changed employers since then, so I'm afraid I can't give an exact example or more detail.



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