Unable to Deque from a Queue

Hi,

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) */
DECLARE
   R_dequeue_options      DBMS_AQ.Dequeue_Options_T;
   r_message_properties   DBMS_AQ.Message_Properties_T;
   V_Message_Handle       RAW (16);
   O_payload               SYS.MGW_BASIC_MSG_T;
BEGIN
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_OUTPUT.put_line ('Begin');
   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
                   );
END;

the same script is working fine with rest of queues..
LVL 16
Wasim Akram ShaikAsked:
Who is Participating?
 
itsme4myselfCommented:
try to drop and recreate the queue.. along with the queue table
0
 
Wasim Akram ShaikAuthor Commented:
yes.. we did the same to resolve this error..
thanks for the input...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.