My Oracle Support Banner

Messages Are Never Moved To The Exception Queue When Rolled Back in sharded queues using JMS/JAVA (Doc ID 2756398.1)

Last updated on MARCH 08, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 19.6.0.0.0 and later
Information 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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.