| Dequeueing messages from exception queues. [message #125770] |
Wed, 29 June 2005 03:11  |
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 #125826 is a reply to message #125820] |
Wed, 29 June 2005 07:18  |
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 |
Frank Naude
Messages: 4581
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
