I am new to the Oracle AQ process and have a few questions about the exception queue. Here's my scenario:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Create Table and Queue:
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE (
QUEUE_TABLE =>'ADD_QUEUE_TABLE',
MULTIPLE_CONSUMERS => TRUE,
QUEUE_PAYLOAD_TYPE => 'ADD_OBJECT_TYPE');
DBMS_AQADM.CREATE_QUEUE (
QUEUE_NAME => 'ADD_QUEUE',
QUEUE_TABLE => 'ADD_QUEUE_TABLE',
max_retries => 6,
retry_delay => 1800);
DBMS_AQADM.START_QUEUE (QUEUE_NAME => 'ADD_QUEUE');
END;
/
To my understanding, it should retry dequeue for 6 times with delay of 30 mins and then send to exception queue. But in my case, it did send the messages to exception queue a lot before. I want your inputs to know if there is something I am missing or doing it wrong.
Enqueue:
CREATE PROCEDURE TXN_ENQUEUE_PROCEDURE (HN_TXN_SEQ IN NUMBER)
AS
ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
MESSAGE_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
MESSAGE_HANDLE RAW (16);
MESSAGE TXN_OBJECT_TYPE;
BEGIN
MESSAGE := TXN_OBJECT_TYPE (HN_TXN_SEQ);
DBMS_AQ.ENQUEUE (QUEUE_NAME => 'FILE_QUEUE',
ENQUEUE_OPTIONS => ENQUEUE_OPTIONS,
MESSAGE_PROPERTIES => MESSAGE_PROPERTIES,
PAYLOAD => MESSAGE,
MSGID => MESSAGE_HANDLE);
COMMIT;
END;
/
Dequeue:
CREATE OR REPLACE PROCEDURE TXN_DEQUEUE_PROCEDURE (
CONTEXT RAW,
REGINFO SYS.AQ$_REG_INFO,
DESCR SYS.AQ$_DESCRIPTOR,
PAYLOAD RAW,
PAYLOADL NUMBER)
AS
DEQUEUE_OPTIONS DBMS_AQ.DEQUEUE_OPTIONS_T;
MESSAGE_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
MESSAGE_HANDLE RAW (16);
MESSAGE TXN_OBJECT_TYPE;
BEGIN
DEQUEUE_OPTIONS.WAIT := DBMS_AQ.NO_WAIT;
DEQUEUE_OPTIONS.CONSUMER_NAME := 'MYSUBSCRIBER';
DEQUEUE_OPTIONS.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;
DEQUEUE_OPTIONS.MSGID := DESCR.MSG_ID;
DEQUEUE_OPTIONS.CONSUMER_NAME := DESCR.CONSUMER_NAME;
LOOP
DBMS_AQ.DEQUEUE (QUEUE_NAME => DESCR.QUEUE_NAME,
DEQUEUE_OPTIONS => DEQUEUE_OPTIONS,
MESSAGE_PROPERTIES => MESSAGE_PROPERTIES,
PAYLOAD => MESSAGE,
MSGID => MESSAGE_HANDLE);
INSERT INTO MESSAGE_TABLE_SAMPLE VALUES (MESSAGE.HN_TXN_SEQ);
XYZ_PACKAGE.ABC_API (MESSAGE.HN_TXN_SEQ); ---- CALL API ----
COMMIT;
END LOOP;
END;
/
When I execute enqueue and if applications goes down or for any other reasons, the messages goes to exception queue. And if I dequeue from the exception queue, do I need to mention ( XYZ_PACKAGE.ABC_API (MESSAGE.HN_TXN_SEQ); ---- CALL API ----) within the dequeue process or it will be automatically called like normal queue dequeue process.
Exception queue :
EXECUTE DBMS_AQADM.START_QUEUE('AQ$_FILE_QUEUE_TABLE_E', false, true);
DECLARE
dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
dq_msgid RAW(16);
payload RAW(1);
no_messages exception;
pragma exception_init (no_messages, -25263);
msg_count number(2);
cursor c_msg_ids is
select msg_id
from aq$FILE_QUEUE_TABLE
where queue = 'AQ$_FILE_QUEUE_TABLE_E';
BEGIN
dequeue_options.consumer_name := null;
dequeue_options.wait := DBMS_AQ.NO_WAIT;
dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
For v_msg_id in c_msg_ids loop
dequeue_options.msgid := v_msg_id.msg_id;
msg_count := 0;
DBMS_AQ.DEQUEUE(queue_name => 'sys.AQ$_FILE_QUEUE_TABLE_E',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => payload,
msgid => dq_msgid);
dbms_output.put_line('Message id : '||v_msg_id.msg_id||' removed');
msg_count := msg_count + 1;
dequeue_options.msgid := null;
dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
END LOOP;
EXCEPTION
WHEN no_messages THEN
DBMS_OUTPUT.PUT_LINE ('No of Messages Removed: '||msg_count);
COMMIT;
END;
/
After executing the above one, it removes the messages from the exception queue. Do I need to mention ( XYZ_PACKAGE.ABC_API (MESSAGE.HN_TXN_SEQ); ---- CALL API ----) anywhere in between?
Is it better to execute exception queue or to have max_retries and with retry_delay?
If the messages move to exception queue, and if I run a query to dequeue them. Do they follow the same mechanism as in dequeue the normal queue and execute any additional apis that uses the message as input?
Thank you!
