Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Dequeue the exception queue in ORACLE AQ

User_XLYERDec 31 2019 — edited Jan 7 2020

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!

This post has been answered by Martien van den Akker on Jan 7 2020
Jump to Answer

Comments

Processing

Post Details

Added on Dec 31 2019
4 comments
6,944 views