Simas.B
Simas.B

Reputation: 754

Oracle AQ set message state to EXPIRED

Is is possible to set message state to DBMS_AQ.EXPIRED without reaching retry_count?

From oracle documentation dbms_aq.message_properties_t.state:

Specifies the state of the message at the time of the dequeue. This parameter cannot be set at enqueue time. The possible states follow:

  • DBMS_AQ.READY: The message is ready to be processed.
  • DBMS_AQ.WAITING: The message delay has not yet been reached.
  • DBMS_AQ.PROCESSED: The message has been processed and is retained.
  • DBMS_AQ.EXPIRED: The message has been moved to the exception queue.

However when i try to dequeue message with state := DBMS_AQ.EXPIRED it's state after dequeue is still DBMS_AQ.PROCESSED.

declare
    procedure mark_as_expired(p_queue_name in varchar2, p_msgid in raw) is
        l_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
        l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;

        l_dummy_payload obj_dummy;
        l_dummy_msgid raw(16);
    begin
        l_dequeue_options.msgid := p_msgid;
        l_dequeue_options.wait := DBMS_AQ.NO_WAIT;
        l_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;

        l_message_properties.state := DBMS_AQ.EXPIRED;

        dbms_aq.dequeue(
            queue_name => sys_context('USERENV', 'CURRENT_USER')||'.'||p_queue_name,
            dequeue_options => l_dequeue_options,
            message_properties => l_message_properties,
            payload => l_dummy_payload,
            msgid => l_dummy_msgid);
    end;
begin
    mark_as_expired('QUEUE_NAME', 'BE3228C90B3239BBE0534980CB0AE63E');
end;

Upvotes: 1

Views: 1376

Answers (0)

Related Questions