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