DEQUEUE

The DEQUEUE procedure dequeues a message. The signature is:

DEQUEUE(
  <queue_name> IN VARCHAR2,
  <dequeue_options> IN DBMS_AQ.DEQUEUE_OPTIONS_T,
  <message_properties> OUT DBMS_AQ.MESSAGE_PROPERTIES_T,
  <payload> OUT <type_name>,
  <msgid> OUT RAW)

Parameters

queue_name

The name (optionally schema-qualified) of an existing queue. If you omit the schema name, the server will use the schema specified in the SEARCH_PATH. Please note that unlike Oracle, unquoted identifiers are converted to lower case before storing. To include special characters or use a case-sensitive name, enclose the name in double quotes.

For detailed information about creating a queue, see DBMS_AQADM.CREATE_QUEUE.

dequeue_options

dequeue_options is a value of the type, dequeue_options_t:

DEQUEUE_OPTIONS_T IS RECORD(
  consumer_name CHARACTER VARYING(30),
  dequeue_mode INTEGER,
  navigation INTEGER,
  visibility INTEGER,
  wait INTEGER,
  msgid BYTEA,
  correlation CHARACTER VARYING(128),
  deq_condition CHARACTER VARYING(4000),
  transformation CHARACTER VARYING(61),
  delivery_mode INTEGER);

Currently, the supported parameter values for dequeue_options_t are:

consumer_name	Must be NULL.
dequeue_mode	The locking behavior of the dequeue operation. Must be either: DBMS_AQ.BROWSE – Read the message without obtaining a lock. DBMS_AQ.LOCKED – Read the message after acquiring a lock. DBMS_AQ.REMOVE – Read the message before deleting the message. DBMS_AQ.REMOVE_NODATA – Read the message, but do not delete the message.
navigation	Identifies the message that will be retrieved. Must be either: FIRST_MESSAGE – The first message within the queue that matches the search term. NEXT_MESSAGE – The next message that is available that matches the first term.
visibility	Must be ON_COMMIT – if you roll back the current transaction the dequeued item will remain in the queue.
wait	Must be a number larger than 0, or: DBMS_AQ.FOREVER – Wait indefinitely. DBMS_AQ.NO_WAIT – Do not wait.
msgid	The message ID of the message that will be dequeued.
correlation	Accepted for compatibility, and ignored.
deq_condition	A VARCHAR2 expression that evaluates to a BOOLEAN value indicating if the message should be dequeued
transformation	Accepted for compatibility, and ignored.
delivery_mode	Must be PERSISTENT; buffered messages are not supported at this time.

message_properties

message_properties is a value of the type, message_properties_t:

message_properties_t IS RECORD(
  priority INTEGER,
  delay INTEGER,
  expiration INTEGER,
  correlation CHARACTER VARYING(128) COLLATE pg_catalog.”C”,
  attempts INTEGER,
  recipient_list “AQ$_RECIPIENT_LIST_T”,
  exception_queue CHARACTER VARYING(61) COLLATE pg_catalog.”C”,
  enqueue_time TIMESTAMP WITHOUT TIME ZONE,
  state INTEGER,
  original_msgid BYTEA,
  transaction_group CHARACTER VARYING(30) COLLATE pg_catalog.”C”,
  delivery_mode INTEGER
DBMS_AQ.PERSISTENT);

The supported values for message_properties_t are:

priority	If the queue table definition includes a sort_list that references priority, this parameter affects the order that messages are dequeued. A lower value indicates a higher dequeue priority.
delay	Specify the number of seconds that will pass before a message is available for dequeueing or NO_DELAY.
expiration	Use the expiration parameter to specify the number of seconds until a message expires.
correlation	Use correlation to specify a message that will be associated with the entry; the default is NULL.
attempts	This is a system-maintained value that specifies the number of attempts to dequeue the message.
recipient_list	This parameter is not supported.
exception_queue	Use the exception_queue parameter to specify the name of an exception queue to which a message will be moved if it expires or is dequeued by a transaction that rolls back too many times.
enqueue_time	enqueue_time is the time the record was added to the queue; this value is provided by the system.
state	This parameter is maintained by DBMS_AQ; state can be: DBMS_AQ.WAITING – the delay has not been reached. DBMS_AQ.READY – the queue entry is ready for processing. DBMS_AQ.PROCESSED – the queue entry has been processed. DBMS_AQ.EXPIRED – the queue entry has been moved to the exception queue.
original_msgid	This parameter is accepted for compatibility and ignored.
transaction_group	This parameter is accepted for compatibility and ignored.
delivery_mode	This parameter is not supported; specify a value of DBMS_AQ.PERSISTENT.

payload

Use the payload parameter to retrieve the payload of a message with a dequeue operation. The payload type must match the type specified when creating the queue table.

msgid

Use the msgid parameter to retrieve a unique message identifier.

Example

The following anonymous block calls DBMS_AQ.DEQUEUE, retrieving a message from the queue and a payload:

DECLARE

  dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_handle     raw(16);
  payload            work_order;

BEGIN
  dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;

  DBMS_AQ.DEQUEUE(
    queue_name         => 'work_queue',
    dequeue_options    => dequeue_options,
    message_properties => message_properties,
    payload            => payload,
    msgid              => message_handle
  );

  DBMS_OUTPUT.PUT_LINE(
  'The next work order is [' || payload.subject || '].'
  );
END;

The payload is displayed by DBMS_OUTPUT.PUT_LINE.