Oracle Built-in Packages

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