Remove expired messages from queue automatically
1007264May 6 2013 — edited Jun 4 2013Hi,
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