Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Remove expired messages from queue automatically

1007264May 6 2013 — edited Jun 4 2013
Hi,
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?

Thanks,
David

Comments

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.

Regards,
David
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 2 2013
Added on May 6 2013
1 comment
3,134 views