Hi,
I have been able to get a solution for this issue. I am posting the details below:
1. 'START' the exception queue and allow dequeueing on it
BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'exception queue name'
,ENQUEUE => FALSE
,DEQUEUE => TRUE
);
END;
/
2. Then use a PL/SQL block like one below to read expired messages from the exception queue:
DECLARE
l_dequeue_options DBMS_AQ.dequeue_options_t;
l_message_properties DBMS_AQ.message_properties
l_message_handle RAW(16);
l_event_msg <event payload type>;
cursor c_exp_mesg is
select MSG_ID
from <queue table name>
where msg_state = 'EXPIRED';
BEGIN
l_dequeue_options.consumer
for rec in c_exp_mesg
LOOP
l_dequeue_options.msgid := rec.msg_id;
DBMS_AQ.dequeue(queue_name
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
-- process your message here in whatever way you want..
END;
/
Main Topics
Browse All Topics
by: anand_2000vPosted on 2009-05-14 at 09:50:03ID: 24386865
Dequeue from Exception queue
Exception Queues
An exception queue is a repository for expired or unserviceable messages. Applications cannot directly enqueue into exception queues. Also, a multiconsumer exception queue cannot have subscribers associated with it. However, an application that intends to handle these expired or unserviceable messages can dequeue them exactly once from the exception queue using remove mode. The consumer name specified while dequeuing should be null. Messages can also be dequeued from the exception queue by specifying the message identifier.
Refer:http://download.orac