Home » Server Options » Streams & AQ » Dequeueing messages from exception queues.
Dequeueing messages from exception queues. [message #125770] Wed, 29 June 2005 03:11 Go to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
I have some messages gone into exception queues because the client program crash without rollback or commit. Can anyboby let me know how to read messages from exception queues. When i tried enabling the de-queue on exception queue, Oracle throws error.
Re: Dequeueing messages from exception queues. [message #125820 is a reply to message #125770] Wed, 29 June 2005 06:56 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Please post you code and the detailed error.

Best regards.

Frank
Re: Dequeueing messages from exception queues. [message #125826 is a reply to message #125820] Wed, 29 June 2005 07:18 Go to previous messageGo to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
Copy pasting what all i did.

SQL> select q_name,state from aq_q_tab33;

Q_NAME STATE
------------------------------ ----------
AQ$_AQ_Q_TAB33_E 3

SQL> declare
2 queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
3 message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
4 message_id RAW(2000);
5 my_message db.soap_message_type;
6 v_queue_name varchar2(40);
7 BEGIN
8 v_queue_name := 'DB.AQ$_AQ_Q_TAB33_E';
9 DBMS_AQ.DEQUEUE(queue_name => v_queue_name,
10 dequeue_options => queue_options,
11 message_properties => message_properties,
12 payload => my_message,
13 msgid => message_id
14 );
15 END;
16 /
declare
*
ERROR at line 1:
ORA-25226: dequeue failed, queue db.AQ$_AQ_Q_TAB33_E is not
enabled for dequeue
ORA-06512: at "SYS.DBMS_AQ", line 221
ORA-06512: at line 9


SQL> exec dbms_aqadm.start_queue(queue_name => 'DB.AQ$_AQ_Q_TAB33_E');
BEGIN dbms_aqadm.start_queue(queue_name => 'DB.AQ$_AQ_Q_TAB33_E'); END;

*
ERROR at line 1:
ORA-24017: cannot enable enqueue on QUEUE, DB.AQ$_AQ_Q_TAB33_E is
an exception queue
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3629
ORA-06512: at "SYS.DBMS_AQADM", line 217
ORA-06512: at line 1

SQL>
Re: Dequeueing messages from exception queues. [message #127540 is a reply to message #125826] Tue, 12 July 2005 02:33 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
I found the following in the "Oracle9i Application Developer's Guide - Advanced Queuing":

"How do I dequeue from an exception queue?

The exception queue for a multiconsumer queue must also be a multiconsumer queue.

Expired messages in multiconsumer queues cannot be dequeued by the intended recipients of the message. However, they can be dequeued in the REMOVE mode once (only once) using a NULL consumer name in dequeue options. Messages can also be dequeued from an exception queue by specifying the message ID.

Expired messages can be dequeued only by specifying message ID if the multiconsumer exception queue was created in a queue table without the compatible parameter or with the compatible parameter set to '8.0'"

Best regards.

Frank
Re: Dequeueing messages from exception queues. [message #128004 is a reply to message #127540] Thu, 14 July 2005 09:39 Go to previous message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Frank ,
Can we have an example pls.


With regards
Dinesh
Previous Topic: How AQ is related to dbms_job
Next Topic: Need to know impact/issues with RAC on Advanced queueing
Goto Forum:
  


Current Time: Thu Mar 28 18:21:15 CDT 2024