Oracle AQ Examples

This section offers numerous examples of using AQ, including packages you can install and reuse in your environment. In all these examples, unless otherwise noted, assume that I have (a) defined an Oracle account named AQADMIN to perform administrative tasks and (b) assigned the AQ_USER_ROLE to SCOTT to perform operational tasks. I then connect to AQADMIN.

After setting up the queue tables and queues, I connect to SCOTT and create this object type:

CREATE TYPE message_type AS OBJECT
   (title VARCHAR2(30),
    text VARCHAR2(2000));

I also grant EXECUTE privilege on this object to my AQ administrator:

GRANT EXECUTE ON message_type TO AQADMIN;

My AQ administrator then can create a queue table and a message queue as follows:

BEGIN
   DBMS_AQADM.CREATE_QUEUE_TABLE
      (queue_table => 'scott.msg',
       queue_payload_type => 'message_type');

   DBMS_AQADM.CREATE_QUEUE
      (queue_name => 'msgqueue',
       queue_table => 'scott.msg');

   DBMS_AQADM.START_QUEUE (queue_name => 'msgqueue');
END;
/

Notice that I do not need to specify the schema for the payload type. AQ assumes the same schema as specified for the queue table.

I will make use of these objects throughout the following examples; I will also at times supplement these queue objects with other, more specialized queue table and queues.

Oracle also provides a set of examples scripts for AQ. In Oracle 8.0.3, the following files were located in $ORACLE_HOME/rdbms80/admin/aq/demo:

aqdemo00.sql

The driver program for the demonstration

aqdemo01.sql

Create queue ...

Get Oracle Built-in Packages now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.