 |
|
Oracle Tips by Burleson |
Chapter 4 -
Chaining Oracle Jobs Together
Conditional Job Runs Using Oracle Advanced
Queuing
Advanced Queuing (AQ) is Oracle’s implementation of a messaging
system which can be used as a replacement for the dbms_pipe
package and other bespoke solutions. The basic unit of any
messaging system is a message with the most important element of the
message being its contents, or payload. In order to define a queue
table, the payload of the messages that will be stored within it
must first be defined. The job_chain_aq_setup.sql script
contains a definition of an object type called job_chain_msg_type
that will act as the payload. The creation of object types requires
the CREATE TYPE privilege.
The payload of the message can be as simple or
complicated as desired. In this case, the only concern is that the
message has been sent. The particular contents are not important at
this time, so the message is extremely simple.
Administration of queues is done using the
dbms_aqadm package and requires the aq_administrator_role
to be granted to the administrator. Alternatively, all
administration can be performed by a privileged user such as SYS or
SYSTEM. With the payload object defined, the queue table is created
using the create_queue_table procedure.
Once the queue table has been created, the
individual queues are created and started using the create_queue
and start_queue procedures respectively. A single queue
table can hold many queues as long as each queue uses the same type
for its payload.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
