I am very new to oracle AQ and trying dequeuing of message from an exception queue. I have created a procedure for dequeuing a message from exception queue and is working fine. I have to execute this procedure manually each time a message is moved to the exception queue. Is there any mechanism to call this procedure automatically each time the message is moved to exception queue? Please help.
Thanks in Advance
if you are familiar with database jobs ( e.g. DBMS_JOB or DBMS_SCHEDULER )
you could setup a procedure to listen at your exception queue ( endless loop with DBMS_AQ.DEQUEUE ).
Every time a row will be inserted you could dequeue this row automatically.
as you wrote you already have a code snippet with DBMS_AQ.DEQUEUE of an exception queue.
Wrap this DEQUEUE in an infinite loop (LOOP ... END LOOP). In order to have this procedure as an autonomous session
you can start it as a database job: