How to Dequeue Messages with DBMS_AQ.DEQUEUE

DBMS_AQ.DEQUEUE takes the next message off a queue, in priority order, and returns its payload and properties. Dequeue options decide how long to wait, which messages qualify, and whether the message is removed or only read. The removal becomes final when the transaction commits.

Code for This Guide

The main examples are in the examples/pkg-messaging folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_aq.dequeue(queue_name, dequeue_options, message_properties_out, payload_out,
                msgid_out);

v_options.wait         := dbms_aq.no_wait;        -- or seconds, or dbms_aq.forever
v_options.navigation   := dbms_aq.first_message;  -- or next_message
v_options.correlation  := 'text';                 -- only matching messages
v_options.dequeue_mode := dbms_aq.remove;         -- or browse, locked

When no message is available within the wait time, DEQUEUE raises ORA-25228.

The example reads the two messages enqueued in How to Enqueue Messages with DBMS_AQ.ENQUEUE.

Empty the Queue

Example:

declare
  v_options dbms_aq.dequeue_options_t;
  v_props   dbms_aq.message_properties_t;
  v_msg     gate_change_t;
  v_msgid   raw(16);
  e_no_more exception;
  pragma exception_init(e_no_more, -25228);          -- timeout or end of fetch
begin
  v_options.wait       := dbms_aq.no_wait;
  v_options.navigation := dbms_aq.first_message;
  loop
    dbms_aq.dequeue('GATE_Q', v_options, v_props, v_msg, v_msgid);
    dbms_output.put_line(v_msg.flight_no || ': gate ' || v_msg.old_gate || ' -> '
                         || v_msg.new_gate || ' (priority ' || v_props.priority || ')');
  end loop;
exception
  when e_no_more then
    commit;                                          -- the dequeued messages are removed
    dbms_output.put_line('queue is empty');
end;
/

Output:

NM150: gate A1 -> B12 (priority 1)
NM101: gate C3 -> C5 (priority 5)
queue is empty

PL/SQL procedure successfully completed.

The loop dequeues without waiting until ORA-25228, named with PRAGMA EXCEPTION_INIT, says the queue is empty. The priority 1 message comes first. The COMMIT in the handler makes the removal final.

Filter and Browse

Example:

declare
  v_enq   dbms_aq.enqueue_options_t;
  v_deq   dbms_aq.dequeue_options_t;
  v_props dbms_aq.message_properties_t;
  v_msg   notice_t;
  v_msgid raw(16);
  v_n     number;
begin
  for r in (select column_value as flight
            from   sys.odcivarchar2list('NM150', 'NM101', 'NM150')) loop
    v_props.correlation := r.flight;
    dbms_aq.enqueue('NOTICE_Q', v_enq, v_props, notice_t(r.flight, 'gate change'), v_msgid);
  end loop;
  commit;

  v_deq.wait        := dbms_aq.no_wait;
  v_deq.correlation := 'NM101';                    -- only messages for NM101
  v_deq.dequeue_mode := dbms_aq.browse;            -- read without removing
  dbms_aq.dequeue('NOTICE_Q', v_deq, v_props, v_msg, v_msgid);
  dbms_output.put_line('browsed: ' || v_msg.flight_no || ' ' || v_msg.text);

  v_deq.dequeue_mode := dbms_aq.remove;            -- now take it off the queue
  dbms_aq.dequeue('NOTICE_Q', v_deq, v_props, v_msg, v_msgid);
  commit;
  select count(*) into v_n from aq$notice_qt;
  dbms_output.put_line('removed ' || v_msg.flight_no || ', ' || v_n || ' messages left');
end;
/

Output:

browsed: NM101 gate change
removed NM101, 2 messages left

PL/SQL procedure successfully completed.

Three messages are enqueued with correlation IDs. With CORRELATION set to NM101 and BROWSE mode, DEQUEUE reads the NM101 message and leaves it on the queue; switching to REMOVE takes it off, leaving the two NM150 messages. The example drops its queue table and type afterward.

Things to Know

• If the transaction rolls back, the message returns to the queue, and its retry count goes up; after MAX_RETRIES it moves to the exception queue.
• Use a WAIT time instead of polling in a loop with sleeps: DEQUEUE wakes up as soon as a message arrives.
• Use FIRST_MESSAGE to see newly arrived messages in priority order on each pass.

Related Guides

• How to Enqueue Messages with DBMS_AQ.ENQUEUE
• How to Name Oracle Errors with PRAGMA EXCEPTION_INIT

Conclusion

DBMS_AQ.DEQUEUE reads and removes queue messages in priority order, waiting as long as you choose. Filter by correlation, browse without removing, and commit to make the removal final, or roll back to return the message for another try.