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:
The driver program for the demonstration
Create queue ...