We have a queue in our system which is behaving weird.
We are unable to deque from the queue, whenever we try to deque we get a exception of
ORA-25235: fetched all messages in current transaction
where in there are messages still lying in the queue..
had used this script to deque..
/* Formatted on 2012/09/11 17:26 (Formatter Plus v4.8.8) */
V_Message_Handle RAW (16);
r_dequeue_options.Wait := Dbms_Aq.No_Wait;
--Start-SP added for multple consumer queue(AQ)
r_dequeue_options.consumer_name := 'VEL_AQ_OU_QUEUE_SUB';
--Each time a select query is fired on the queue when first_message option is used
r_dequeue_options.navigation := Dbms_Aq.First_Message; --sp
DBMS_AQ.dequeue (queue_name => 'AQADMIN.VEL_AQ_OU_QUEUE',
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
Payload => o_payload,
msgid => v_message_handle
the same script is working fine with rest of queues..