Now that we know how to create tables and queues and enqueue messges, let’s look at how to dequeue them!
Dequeuing Features
Oracle AQ provides very high performance and functionality. Key features include:
- Concurrent dequeues
- Multiple dequeue methods and options
- Array dequeue
- Message navigation
- Waiting for messages
- Retries with delays
- Transaction protection
- Exception queues
DBMS_AQ.DEQUEUE
The PL/SQL API is:
DBMS_AQ.DEQUEUE(
queue_name IN VARCHAR2,
dequeue_options IN dequeue_options_t,
message_properties OUT message_properties_t,
payload OUT "type_name",
msgid OUT RAW);
Note that message_properties_t is used for both enqueue and dequeue operations.
DEQUEUE_OPTIONS_T
TYPE DEQUEUE_OPTIONS_T IS RECORD (
consumer_name VARCHAR2(30) DEFAULT NULL,
dequeue_mode BINARY_INTEGER DEFAULT REMOVE,
navigation BINARY_INTEGER DEFAULT NEXT_MESSAGE,
visibility BINARY_INTEGER DEFAULT ON_COMMIT,
wait BINARY_INTEGER DEFAULT FOREVER,
msgid RAW(16) DEFAULT NULL,
correlation VARCHAR2(128) DEFAULT NULL,
deq_condition VARCHAR2(4000) DEFAULT NULL,
signature aq$_sig_prop DEFAULT NULL,
transformation VARCHAR2(61) DEFAULT NULL,
delivery_mode PLS_INTEGER DEFAULT PERSISTENT);
The DBMS.AQ.DEQUEUE_OPTIONS_T specifies the dequeuing options as described below:
- consumer_name – the name of the subscriber.
- dequeue_mode – modes includes:
- REMOVE (with data) – this is the typical dequeuing method. The message may remain in the queue table for history based on retention period, but it not eligible for future dequeuing (unless via msg_id).
- REMOVE_NODATA – no data is returned, but the message is removed from queue. This may be used for selective cleanup.
- BROWSE – reads the message data, but does not actually dequeue the message. The message remains available for future processing (unless dequeued by another process). Browsing may not be repeatable, and as such there are numerous “gotchas” to be aware of.
- navigation – there are two methods for navigation when dequeuing.
- FIRST_MESSAGE – This creates a “snapshot” (effectively a cursor); note that this only retrieves messages that were enqueued at the time of the dequeue call.
- NEXT_MESSAGE – If FIRST_MESSAGE was used, this retrieves the next message in the snapshot. See Default Dequeuing
- wait – if no messages are available, the consumer may wait for the next message. The options are:
- FOREVER – waits forever, which is the default. Typically used for high frequency queues. Note that this blocks the process.
- NO_WAIT – don’t wait for next message. Typically used for deferred or batch operations, which are initiated by jobs scheduled at regular intervals.
- Number – the wait time in seconds. Process is blocked while waiting.
The next message is dequeued on wake up.
NOTE BENE: Oracle AQ also offers the ability for a process to listen on multiple queues.
Dequeue Methods
There are several methods for dequeuing messages. The default is to dequeue individual messages based on the sort order specified when the AQ table was created.
NOTE BENE: the most efficient navigation method for dequeuing based on the sort order is to use NEXT_MESSAGE without FIRST_MESSAGE. FIRST_MESSAGE always performs a query. However, if NEXT_MESSAGE is used without FIRST_MESSAGE, it will only perform one SELECT in the session; subsequent calls are simple fetches.
Other methods are:
- Correlation ID – dequeue series of message based on correlation as follows:
- Get correlation id by dequeuing using FIRST_MESSAGE. Dequeue additional messages via NEXT_MESSAGE using the value of correlation until no more messages remain.
The specification for correlation may use pattern matching (%,_).
This method typically requires the addition of an index and generation of statistics to force the underlying queries to use the index on the correlation column..
- Transaction group – similar to correlation, but uses transaction_group set by producer. Should use array dequeuing, but may use same loop as Correlation ID above, but specifying the transaction_group. Pattern matching may also be used.
- deq_condition– similar to SQL WHERE clause, accesses contents of payload object elements or other columns. See documentation for more details about specifying columns and payload elements. Note that using the method supersedes all other methods.
- msgid – dequeue a single message by system-assigned RAW value. This typically requires browsing the queue(s), and is usually used for cleanup and corrections.
Dequeue visibility
Messages may be dequeued in the following modes:
- IMMEDIATE – Messages are removed from the queue in an autonomous transaction. If the application does not have retry capabilities, this will typically offer better performance and scalability
- ON_COMMIT (transaction protection) – Messages are removed from the queue on COMMIT of the transaction. The dequeue operation is treated in the same manner as an INSERT/UPDATE/DELETE. If the transaction fails, either due to ROLLBACK, system failure or shutdown, the retry count is incremented. If the retry count is exceeded, the message is moved to the exception queue, otherwise it remains in the original queue. Note that a system failure or shutdown may not increment the retry count. If retry_delay was specified when the queue was created, the message will not be available for dequeuing for the specified number of seconds.
Message Expiration
If expiration is specified in message_properties_t.expiration, all consumers must dequeue the message before expiration time. Otherwise, the message is moved to the exception queue. It is generally a good practice to specify expiration for multi-consumer queues, as not all consumers may be active, which would result in the message remaining in the queue indefinitely.<cod
