Jobs are often defined as individual tasks that
are performed in isolation.
Yet in some circumstances, a job consists
of several tasks that must be performed as a
whole in a specific sequence.
Typically, this would be accomplished by
combining the tasks into a single job like the
one defined below.
DBMS_SCHEDULER.create_job
(
job_name
=> 'single_job',
job_type
=> 'PLSQL_BLOCK',
job_action
=> ‘BEGIN
task1;
task2;
task3;
END;’,
start_date
=> SYSTIMESTAMP,
repeat_interval => ‘freq=daily;
byhour=9; byminute=0; bysecond=0;’,
end_date
=> NULL,
enabled
=> TRUE,
comments
=> 'Single job.');
The problem arises when not all tasks can be
performed at the same time.
For example, a batch of orders might
process at midnight and produce the necessary
billing paperwork at 9:00 a.m.
If no dependencies are defined between
these tasks, any delays in the order processing
may result in the generation of the billing
paperwork before the orders are complete.
In these circumstances, a job chain needs to be
created such that each task in the chain is
performed in sequence and the failure of a
single task breaks the chain.
This can be achieved in many ways, but
the following methods are preferred:
Conditional Job Creation
In this method, the first task in the chain is
scheduled as a regular repeating job, but all
subsequent tasks are not scheduled. Instead, as
each task in the chain completes successfully,
it schedules the next task as a one-off job.
In the order and billing example, the time
between tasks was long and the run times were
fixed, excluding delays.
An example like this would not be very
useful here since it would require a significant
amount of time for the chain to complete
successfully.
Instead, assume that a process made up of
three tasks must run in sequence.
For the purposes of testing, the times
between tasks should be relatively short and
instead of fixed times, rolling times should be
used.
In this example, each task will simply insert a
record into a table, which can be created using
the following script:
CREATE TABLE job_chain (
created_timestamp
TIMESTAMP,
task_name
VARCHAR2(20)
);
The
job_chain_create.sql
script creates a package specification and body
that will do all the work for the example job
chain.
CREATE OR REPLACE PACKAGE
job_chain_create AS
PROCEDURE task_1;
PROCEDURE task_2;
PROCEDURE task_3;
END job_chain_create;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE
BODY job_chain_create AS
--
-----------------------------------------------------------------
PROCEDURE task_1 AS
--
-----------------------------------------------------------------
BEGIN
DELETE FROM job_chain;
INSERT INTO job_chain (created_timestamp,
task_name)
VALUES (systimestamp, 'TASK_1');
COMMIT;
-- Uncomment the following line to
force a failure.
--RAISE_APPLICATION_ERROR(-20000,
--
'This is a fake error to prevent
task_2 being executed');
-- The work has comleted successfully
so create task_2
-- Oracle
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_create_task_2',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_create.task_2',
start_date
=> SYSTIMESTAMP + INTERVAL '2'
MINUTE,
repeat_interval => NULL,
end_date
=> NULL,
enabled
=> TRUE,
comments
=> 'Second task in the create
chain.');
SHOW ERRORS
Both task_1 and task_2 schedule a one-off job
once successfully completed.
Any exceptions are caught by the
exception handler, which does not schedule the
next job in the chain.
With the table and code in place, a job to call
the first task using the
job_chain_create_job.sql
script can be scheduled.
-- Oracle
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_create_task_1',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_create.task_1',
start_date
=> SYSTIMESTAMP,
repeat_interval => NULL,
end_date
=> NULL,
enabled
=> TRUE,
comments
=> 'First task in the create
chain.');
END;
/
*/
The
repeat_interval
(or
interval) parameter of this job
definition is set to NULL, making it a one-off
job.
Under normal circumstances, this job is
expected to be scheduled with a repeat interval
since it is the first task in the chain.
However, for the purposes of this example, the
less clutter on the system the better, so no
unnecessary repeating jobs are scheduled.
The progress of the job can be monitored using
the following query:
alter session set
nls_timestamp_format = 'DD-MON-YYYY
HH24:MI:SS.FF';
set linesize 100
column created_timestamp
format a27
column task_name format
a20
select
*
from
job_chain
order by
created_timestamp
;
On completion of the chain, the following output
from the query is expected:
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
---------------------------
--------------------
07-AUG-2004
10:49:42.701000 TASK_1
07-AUG-2004
10:51:42.858000 TASK_2
07-AUG-2004
10:53:43.093000 TASK_3
The result of breaks in the chain can be tested
by uncommenting the lines in the code containing
the
raise_application_error
procedure calls.
Uncommenting this line in task_1 would
cause the chain to break during task_1,
resulting in the following query output:
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
---------------------------
--------------------
07-AUG-2004
11:03:11.827000 TASK_1
Commenting out the statement in task_1 and
uncommenting it in task_2 would cause the chain
to break in task_2, resulting in the following
query output:
CREATED_TIMESTAMP
TASK_NAME
---------------------------
--------------------
07-AUG-2004
11:10:42.746000 TASK_1
07-AUG-2004
11:12:42.956000 TASK_2
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
