[Appendix C] C.3 Oracle AQ, the Advanced Queueing Facility

C.3 Oracle AQ, the Advanced Queueing Facility

Oracle8 offers the Oracle Advanced Queuing facility (Oracle AQ) which implements deferred execution of work. There are two packages you will use to implement advanced queuing: DBMS_AQ, which contains the queuing procedures themselves, and DBMS_AQADM, which lets you perform administrative tasks. They make extensive use of PL/SQL record structures, as you will see in the individual program interfaces below. For more detail on these records and how to manipulate their contents, see Oracle Built-in Packages.

C.3.1 DBMS_AQ (PL/SQL 8 Only)

The DBMS_AQ package provides an interface to the messaging tasks of Oracle AQ. To use these procedures, you must have been granted the new role, AQ_USER_ROLE.

C.3.1.1 The ENQUEUE procedure

The ENQUEUE procedure adds a message to an existing message queue. The target message queue must have had enqueuing enabled previously via the DBMS_ AQADM.START_QUEUE procedure. The specification is:

PROCEDURE DBMS_AQ.ENQUEUE
  (q_schema IN VARCHAR2 DEFAULT NULL
   q_name IN VARCHAR2,
   corrid IN VARCHAR2 DEFAULT NULL,
   transactional IN BOOLEAN:= TRUE,
   priority IN POSITIVE DEFAULT 1,
   delay IN DATE DEFAULT NULL,
   expiration IN NATURAL:= 0,
   relative_msgid IN NUMBER DEFAULT NULL,
   seq_deviation IN CHAR DEFAULT A,
   exception_queue_schema IN VARCHAR2 DEFAULT NULL,
   exception_queue IN VARCHAR2 DEFAULT NULL,
   reply_queue_schema IN VARCHAR2 DEFAULT NULL,
   reply_queue IN VARCHAR2 DEFAULT NULL,
   user_data IN any_object_type,
   msgid OUT RAW);

C.3.1.2 The DEQUEUE procedure

The DEQUEUE procedure can either remove or browse a message from an existing message queue. The target message queue must have had dequeuing enabled previously via the DBMS_AQADM.STOP_QUEUE procedure. The specification is:

PROCEDURE DBMS_AQ.DEQUEUE 
  (q_schema IN VARCHAR2 DEFAULT NULL, 
   q_name IN VARCHAR2, 
   msgid IN RAW DEFAULT NULL, 
   corrid IN VARCHAR2 DEFAULT NULL, 
   deq_mode IN CHAR DEFAULT `D', 
   wait_time IN NATURAL DEFAULT NULL, 
   transactional IN BOOLEAN:= true, 
   out_msgid OUT NUMBER, 
   out_corrid OUT VARCHAR2, 
   priority OUT POSITIVE, 
   delay OUT DATE, 
   expiration OUT NATURAL, 
   retry OUT NATURAL, 
   exception_queue_schema OUT VARCHAR2, 
   exception_queue OUT VARCHAR2, 
   reply_queue_schema OUT VARCHAR2, 
   reply_queue OUT VARCHAR2, 
   user_data OUT any_object_type);

C.3.2 DBMS_AQADM (PL/SQL 8 Only)

The DBMS_AQADM package provides an interface to the administrative tasks of Oracle AQ. To use these procedures, a DBMS_AQADM user must have been granted the new role, AQ_ADMINISTRATOR_ROLE. You can verify the results of executing the DBMS_ AQADM package by querying the new Oracle AQ data dictionary views, USER_QUEUE_ TABLES and USER_QUEUES (DBA levels of these views are also available).

C.3.2.1 The CREATE_QUEUE_TABLE procedure

The CREATE_QUEUE_TABLE procedure creates a queue table. A queue table is the named repository for a set of queues and their messages. A queue table may contain numerous queues, each of which may have many messages. But a given queue and its messages may exist in only one queue table. The specification is:

PROCEDURE DBMS_AQADM.CREATE_QUEUE_TABLE
  (queue_table IN VARCHAR2
   ,queue_payload_type IN VARCHAR2
   ,storage_clause IN VARCHAR2 DEFAULT NULL
   ,sort_list IN VARCHAR2 DEFAULT NULL
   ,multiple_consumers IN BOOLEAN DEFAULT FALSE
   ,message_grouping IN BINARY_INTEGER DEFAULT NONE
   ,comment IN VARCHAR2 DEFAULT NULL
   ,auto_commit IN BOOLEAN DEFAULT TRUE);

C.3.2.2 The DROP_QUEUE_TABLE procedure

The DROP_QUEUE_TABLE procedure drops an existing queue table. An error is returned if the queue table does not exist. The force parameter specifies whether all existing queues in the queue table are stopped and dropped automatically or manually. If manually (i.e., FALSE), then the queue administrator must stop and drop all existing queues within the queue table using the DBMS_AQADM.STOP_QUEUE and DBMS_AQADM.DROP_QUEUE procedures. The specification is:

PROCEDURE DBMS_AQADM.DROP_QUEUE_TABLE
  (queue_table IN VARCHAR2,
   force IN BOOLEAN default FALSE,
   auto_commit IN BOOLEAN default TRUE);

C.3.2.3 The CREATE_QUEUE procedure

The CREATE_QUEUE procedure creates a new message queue within an existing queue table. An error is returned if the queue table does not exist. The required queue_name parameter specifies the name of the new message queue to create. All queue names must be unique within the schema. The specification is:

PROCEDURE DBMS_AQADM.CREATE_QUEUE
  (queue_name IN VARCHAR2,
   queue_table IN VARCHAR2,
   queue_type IN BINARY_INTEGER default DBMS_AQADM.NORMAL_QUEUE,
   max_retries IN NUMBER default 0,
   retry_delay IN NUMBER default 0,
   retention_time IN NUMBER default 0,
   dependency_tracking IN BOOLEAN default FALSE,
   comment IN VARCHAR2 default NULL,
   auto_commit IN BOOLEAN default TRUE);

C.3.2.4 The ALTER_QUEUE procedure

The ALTER_QUEUE procedure modifies properties of an existing message queue. It returns an error if the message queue does not exist. Currently, you can alter only the maximum retries, retry delay, retention time, rentention delay and auto-commit properties; Oracle will augment this list in future releases. The specification is:

PROCEDURE DBMS_AQADM.ALTER_QUEUE (
   queue_name IN VARCHAR2,
   max_retries IN NUMBER default NULL,
   retry_delay IN NUMBER default NULL,
   retention_time IN NUMBER default NULL,
   auto_commit IN BOOLEAN default TRUE);

C.3.2.5 The DROP_QUEUE procedure

The DROP_QUEUE procedure drops an existing message queue. It returns an error if the message queue does not exist. DROP_QUEUE is not allowed unless STOP_QUEUE has been called to disable both enqueuing and dequeuing for the message queue to be dropped. If the message queue has not been stopped, then DROP_QUEUE returns an error of queue resource busy. The specification is:

PROCEDURE DBMS_AQADM.DROP_QUEUE_TABLE
  (queue_table IN VARCHAR2,
   force IN BOOLEAN default FALSE,
   auto_commit IN BOOLEAN default TRUE);

C.3.2.6 The START_QUEUE procedure

The START_QUEUE procedure enables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to enable both. The specification is:

PROCEDURE DBMS_AQADM.START_QUEUE (
   queue_name IN VARCHAR2,
   enqueue IN BOOLEAN DEFAULT TRUE,
   dequeue IN BOOLEAN DEFAULT TRUE);

C.3.2.7 The STOP_QUEUE procedure

The STOP_QUEUE procedure disables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to disable both enqueuing and dequeuing. The wait parameter specifies whether to wait for outstanding transactions or to return immediately. The wait option is highly dependent on outstanding transactions. If outstanding transactions exist, then wait will either hang until the transactions complete or return an error of ORA-24203, depending on whether the wait parameter is set to true or false. The specification is:

PROCEDURE DBMS_AQADM.STOP_QUEUE 
  (queue_name IN VARCHAR2, 
   enqueue IN BOOLEAN DEFAULT TRUE, 
   dequeue IN BOOLEAN DEFAULT TRUE, 
   wait IN BOOLEAN DEFAULT TRUE);


C.2 DBMS_ALERT		C.4 DBMS_DDL