1 Reply Latest reply on Jun 4, 2013 5:52 AM by davidp 2

    Remove expired messages from queue automatically

      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
          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.