Dequeue the exception queue in ORACLE AQ

User_XLYER

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!

Martien van den Akker

Hi,

I think there are a few flaws in your setup.

1. You should not commit within a procedure: in your dequeue procedure you have a commit within the loop. You should not do that. With AQ you can have a visibility attribute for the enqueue or dequeue properties. This attribute can be dbms_aq.on_commit or dbms_aq.immediate. With immediate the enqueue or dequeue is implicitly committed. With on_commit it is done within the transaction of the procedure.
2. I think you should combine the insert with the XYZ_PACKAGE.ABC_API. Combine them into a compound procedure. Make sure that you have proper exception handling, in a way that an exception in either the insert or the api will rollback not only the api and the insert but also the dequeue.
3. You just dequeue from the exception queue. There's nothing done with the dequeued messages. They get on the exception queue because the dequeue failed, probably because either the insert or the API failed. So after a dequeue from the exception queue those should be done as well. I think it's best to dequeue from the exception queue and then enqueue them to the normal queue. But consider if you need to take poison pills into account (messages on the exception queue that aren't processable at all).
4. In the dequeue-loop you don't take consider the situation that the queue is empty. You should

By the way: by default AQ does has a retry of 5. But you can change that.

Kind regards,
Martien

Gaz in Oz

Consider moving your question/thread here to perhaps get more responses:

User_XLYER

@AndrewSayer Can you help me here?

Martien van den Akker

Hi,

I think there are a few flaws in your setup.

1. You should not commit within a procedure: in your dequeue procedure you have a commit within the loop. You should not do that. With AQ you can have a visibility attribute for the enqueue or dequeue properties. This attribute can be dbms_aq.on_commit or dbms_aq.immediate. With immediate the enqueue or dequeue is implicitly committed. With on_commit it is done within the transaction of the procedure.
2. I think you should combine the insert with the XYZ_PACKAGE.ABC_API. Combine them into a compound procedure. Make sure that you have proper exception handling, in a way that an exception in either the insert or the api will rollback not only the api and the insert but also the dequeue.
3. You just dequeue from the exception queue. There's nothing done with the dequeued messages. They get on the exception queue because the dequeue failed, probably because either the insert or the API failed. So after a dequeue from the exception queue those should be done as well. I think it's best to dequeue from the exception queue and then enqueue them to the normal queue. But consider if you need to take poison pills into account (messages on the exception queue that aren't processable at all).
4. In the dequeue-loop you don't take consider the situation that the queue is empty. You should

By the way: by default AQ does has a retry of 5. But you can change that.

Kind regards,
Martien

User_XLYER

Thanks a lot for your valuable inputs. That should get me started.