In this method, all tasks in the chain are
scheduled as regular repeating jobs.
When a task completes successfully, it
places a message on a queue for the next task to
read.
With the exception of the first task, the
first operation a task performs is read from its
queue.
If there is a message on the queue, the
task can proceed; otherwise, it waits
indefinitely for the message to arrive.
Before any code can be written, a queuing
infrastructure needs to be set up using the
job_chain_aq_setup.sql
script and background information must be
introduced.
A full introduction to Oracle Advanced
Queuing
is beyond the scope of this book, so
explanations will be limited to just those
elements necessary to build a simple working
system.
-- Grant necessary
permissions
conn sys/password as
sysdba
-- Create the queue
payload
CREATE OR REPLACE TYPE
job_user.job_chain_msg_type AS OBJECT (
message
VARCHAR2(10)
)
/
-- Create the queue table
and queues
BEGIN
DBMS_AQADM.create_queue_table (
queue_table
=>
'job_user.job_chain_queue_tab',
queue_payload_type
=>
'job_user.job_chain_msg_type');
DBMS_AQADM.create_queue (
queue_name
=>
'job_user.task_2_queue',
queue_table
=>
'job_user.job_chain_queue_tab');
DBMS_AQADM.create_queue (
queue_name
=>
'job_user.task_3_queue',
queue_table
=>
'job_user.job_chain_queue_tab');
DBMS_AQADM.start_queue (
queue_name
=> 'job_user.task_2_queue',
enqueue
=> TRUE);
DBMS_AQADM.start_queue (
queue_name
=> 'job_user.task_3_queue',
enqueue
=> TRUE);
END;
/
grant execute on dbms_aq
to job_user;
conn job_user/job_user
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.
Messages are queued and dequeued using the
dbms_aq
package.
Access to this package can be granted
using the
aq_user_role
role.
However, access to it from a stored procedure is
achieved by using the
job_chain_aq_setup.sql
script. This grants the privilege on this object
directly to the test user.
The contents of the queue table can be monitored
using the
job_chain_aq_query.sql
script.
select
queue,
count(*) as messages
from
aq$job_chain_queue_taB
group by
queue
order by
queue
;
