Messages Are Never Moved To The Exception Queue When Rolled Back in sharded queues using JMS/JAVA
(Doc ID 2756398.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 19.6.0.0.0 and laterInformation in this document applies to any platform.
Symptoms
Messages are RETRYEXPIRED in queue and not moved to exception queue when you created exception queue explicitly for sharded queues
Changes
In Pl/SQL code it work without issues :
--------------------------------------------
SQL> declare
props dbms_aqadm.queue_props_t;
queue_name varchar2(32) := 'TEST_QUEUE';
begin
dbms_aqadm.create_sharded_queue(
queue_name => queue_name,
queue_properties => props);
dbms_aqadm.create_exception_queue(queue_name, queue_name || '_E'); --------->Exception queue creating for queue.
dbms_aqadm.start_queue(queue_name => queue_name);
end;
/
PL/SQL procedure successfully completed.
SQL> declare
message sys.aq$_jms_text_message;
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid raw(16);
begin
message :=sys.aq$_jms_text_message.construct;
message.set_text('text message pl/sql');
message_properties.exception_queue := 'TEST_QUEUE_E'; -----/*At enqueue time need to specify exception queue detail, Once messages expired it will go to exception queues*/
dbms_aq.enqueue( queue_name => 'TEST_QUEUE',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => msgid);
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select count(*), MSG_STATE, QUEUE from AQ$TEST_QUEUE group by MSG_STATE, QUEUE;
COUNT(*) MSG_STATE QUEUE
---------- ------------ ----------
1 READY TEST_QUEUE
SQL> DECLARE
dequeue_msgid RAW(16);
dequeue_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
message_props DBMS_AQ.MESSAGE_PROPERTIES_T;
deq_msg SYS.aq$_jms_text_message;
payload VARCHAR2(512);
BEGIN
dequeue_opts.consumer_name := NULL;
dequeue_opts.navigation := DBMS_AQ.first_message;
dequeue_opts.wait := 0;
message_props.exception_queue := 'TEST_QUEUE_E'; ------------->/*Dequeue time need to specify exception queue name, So if any messages expired it will move to exception queue */
DBMS_AQ.dequeue(queue_name => 'TEST_QUEUE',
dequeue_options => dequeue_opts,
message_properties => message_props,
payload => deq_msg,
msgid => dequeue_msgid);
deq_msg.get_text(payload);
DBMS_OUTPUT.put_line(payload);
ROLLBACK;
END;
/
PL/SQL procedure successfully completed.
SQL> select count(*), MSG_STATE, QUEUE from AQ$TEST_QUEUE group by MSG_STATE, QUEUE;
COUNT(*) MSG_STATE QUEUE
---------- ------------ ----------
1 RETRYEXPIRED TEST_QUEUE
SQL> select count(*), MSG_STATE, QUEUE,EXCEPTION_QUEUE from AQ$TEST_QUEUE group by MSG_STATE, QUEUE,EXCEPTION_QUEUE;
1 RETRYEXPIRED TEST_QUEUE TEST_QUEUE_E ----------->Messages are expired and moved in exception queues
In JMS/JAVA its not Working :
-------------------------------
While dequeue messages through JMS/JAVA using message.setStringProperty("JMS_OracleExcpQ", "<explicity_created_exception_queue_name>_E”) property, The messages are not moving to exception queue. Its due to BUG 32423985 - MESSAGES ARE NEVER MOVED TO THE EXCEPTION QUEUE WHEN ROLLED BACK IN SHARDED QUEUE USING JMS/JAVA
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |