oracle-developer.net

introduction to advanced queuing

Advanced Queuing (AQ) has been available for several versions of Oracle. It is Oracle's native messaging software and is being extended and enhanced with every release. This article provides a high-level overview of Advanced Queuing (known as Streams AQ in 10g). In particular, we will see how to setup a queue for simple enqueue-dequeue operations and also create automatic (asynchronous) dequeuing via notification.

Note that AQ supports listening for messages from outside the database (such as JMS queues). As this article is introductory in nature, we will not cover this functionality. Instead we will concentrate solely on in-database messaging.

requirements

The examples in this article require the following specific roles and privileges (in addition to the more standard CREATE SESSION/TABLE/PROCEDURE/TYPE and a tablespace quota):

AQ_ADMINISTRATOR_ROLE: to create queue tables and queues; and

EXECUTE ON DBMS_AQ: to enable compilation of a PL/SQL procedure during the notification example.

The examples in this article can be run under any user with the above privileges. I have specifically ensured that schema qualifiers are excluded from all DBMS_AQADM procedure calls (many procedures in DBMS_AQADM require us to specify the names of schema objects to be created or dropped. The schema name can optionally be included to create the objects in another schema, but defaults to current schema if excluded).

creating and starting a queue

AQ handles messages known as "payloads". The format and structure of the messages are designed by us and can be either user-defined objects or instances of XMLType or ANYDATA (as of 9i). When we create a queue, we need to tell Oracle the payload structure, so we'll begin by creating a very simple object type for our messages.

Our payload type contains just one attribute. In real applications, our payloads are likely to be far more complex in structure. Now we have the payload defined, we can create a queue table. This table will be used by Oracle to store queued messages until such time that they are permanently dequeued. Queue tables are created using the DBMS_AQADM package as follows.

By now, we have created a queue payload, a queue table and a queue itself. We can see what objects DBMS_AQADM has created in support of our queue. Note that the payload type is excluded as we created it explicitly ourselves.

We can see that a single queue generates a range of system-generated objects, some of which can be of direct use to us, as we will see later. Interestingly, a second queue is created. This is known as an exception queue. If AQ cannot retrieve a message from our user-queue, it will be placed on the exception queue.

enqueuing messages

We are now ready to enqueue a single message using the DBMS_AQ.ENQUEUE API. In the following example, we enqueue a single message using default options for the ENQUEUE procedure. DBMS_AQ has a wide range of record and array types to support its interfaces and to enable us to modify its behaviour (we can see two of these referenced in the example below).

We can see that enqueuing a message is very simple. The enqueue operation is essentially a transaction (as it writes to the queue table), hence we needed to commit it.

browsing messages

Before we dequeue the message we just placed on the queue, we'll "browse" the queue contents. First we can query the AQ$DEMO_QUEUE_TABLE view to see how many messages there are to be dequeued. As we saw earlier, this view was created automatically by DBMS_AQADM.CREATE_QUEUE_TABLE when we created our queue.

SQL> SELECT COUNT(*)
2 FROM aq$demo_queue_table;

COUNT(*)
----------
1

As expected, we have just one message on our queue. We can browse the contents of the enqueued messages via this view without taking them off the queue. We have two methods for browsing. First, we can query the view directly as follows.

SQL> SELECT user_data
2 FROM aq$demo_queue_table;

USER_DATA(MESSAGE)
------------------------------------------------------------
DEMO_QUEUE_PAYLOAD_TYPE('Here is a message')

Second, we can use the DBMS_AQ.DEQUEUE API to browse our messages. We haven't seen the DEQUEUE API up to this point, but as its name suggests, it's the DBMS_AQ procedure for dequeuing messages. As with the ENQUEUE API, the DEQUEUE procedure accepts a range of options and properties as parameters. To browse messages without removing them from the queue, we can modify the dequeue properties to use the constant DBMS_AQ.BROWSE (default is DBMS_AQ.REMOVE).

We can easily confirm that our data hasn't been dequeued by browsing as follows.

SQL> SELECT user_data
2 FROM aq$demo_queue_table;

USER_DATA(MESSAGE)
------------------------------------------------------------
DEMO_QUEUE_PAYLOAD_TYPE('Here is a message')

dequeuing messages

Now we will actually dequeue the message. This doesn't have to be from the same session (remember that enqueues are committed transactions and AQ is table-based). Like the enqueue, the dequeue is a transaction (removing the message from the queue table). If we are happy with the message, we must commit the dequeue.

notification

For the remainder of this article, we will look at automatic dequeue via notification. By this we mean that whenever a message is enqueued, Oracle will notify an agent to execute a registered PL/SQL "callback" procedure (alternatively, the agent can notify an email address or http:// address rather than execute a callback procedure).

For our demonstration, we'll create and register a PL/SQL procedure to manage our dequeue via notification. This callback procedure will dequeue the message and write it to a database table, to simulate the type of standard
in-database operation that callback procedures are used for.

To begin, we'll clear down the objects created for the previous examples. The supported method is via DBMS_AQADM only as follows.

Now we can re-create the queue table to allow multiple consumers. A consumer is an agent that dequeues messages (i.e. reads them off the queue). Enabling multiple consumers is a pre-requisite for automatic notification.

Now we have an application table, we can create our callback PL/SQL procedure. This procedure will dequeue the enqueued message that triggered the notification. The parameters must be named and typed as shown. The enqueued message will include the enqueue timestamp, as will the insert into our application table. This will give us an idea of the asynchronous delay between the message enqueue and the notification for dequeue.

We are not quite finished with our notification setup yet. We need to add a named subscriber to the queue and register the action that the subscriber will take on notification (i.e. it will execute our callback procedure). We add and register the subscriber as follows.

To see if our message was automatically dequeued, we'll check our application table (DEMO_QUEUE_MESSAGE_TABLE). Remember that this is the table that the callback procedure will insert the dequeued message into. In running these examples, it might be necessary to sleep for a short period, because the dequeue is asynchronous and runs as a separate session in the background.

We can see that the asynchronous dequeue via notification occurred approximately 5 seconds after our enqueue operation.

further reading

We've only just touched on AQ's capabilities in this article. AQ is an enormous application that covers a broad range of scenarios and requirements that are far more sophisticated than this simple introduction. For more information on its potential uses, in addition to its syntactic usage, refer to the Advanced Queuing Guide in the online documentation (or equivalent for your database version).