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