DEQUEUE v13

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 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 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.

← Prev

ENQUEUE

DEQUEUE v13

← Prev

Next →