• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1002
  • Last Modified:

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..
0
Wasim Akram Shaik
Asked:
Wasim Akram Shaik
1 Solution
 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now