Hi all,
I am new to the Oracle AQ process and have a few questions about the exception queue. Here's my scenario:
- OBJECT (hn_SEQ)
- Enque_procedure and a dequeue_procedure
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Queue:
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE ( QUEUE_TABLE => FILE_QUEUE_TABLE', MULTIPLE_CONSUMERS => TRUE, QUEUE_PAYLOAD_TYPE => 'FILE_TXN_OBJECT_TYPE');
DBMS_AQADM.CREATE_QUEUE ( QUEUE_NAME => 'FILE_QUEUE', QUEUE_TABLE => FILE_QUEUE_TABLE');
DBMS_AQADM.START_QUEUE (QUEUE_NAME => 'FILE_QUEUE');
END;
/
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?
On top of that I have this to setup queue table and its queues.
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE (
QUEUE_TABLE =>ADDRESS_QUEUE_TABLE',
MULTIPLE_CONSUMERS => TRUE,
QUEUE_PAYLOAD_TYPE => 'ADDRESS_OBJECT_TYPE');
DBMS_AQADM.CREATE_QUEUE (
QUEUE_NAME => 'ADDRESS_QUEUE',
QUEUE_TABLE => 'ADDRESS_QUEUE_TABLE',
max_retries => 6,
retry_delay => 1800);
DBMS_AQADM.START_QUEUE (QUEUE_NAME => 'ADDRESS_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.
Thank you!