Oracle PL/SQL Programming, Second Edition
Replacement text for Appendix C, pages 926-930
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.
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.
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);
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);
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).
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);
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);
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);
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);
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);
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);
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);
Return to Corrections and Amplifications