Chapter 4 -
Chaining Oracle Jobs Together
||Oracle Tips by Burleson
Creating a Job Chain
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.
Conditional job enabling.
Conditional job runs using Oracle
Conditional job runs using a
custom table solution.
Most of the examples in this chapter will use
the Oracle10g dbms_scheduler package, but there are earlier
versions of Oracle in which the dbms_job package can be used
effectively in its place.
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.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA