Search notes:

Oracle: Chains

A chain is a scheduler object with which dependent scheduling is possible: Jobs are then started depending on the outcomes of one or more other jobs, according to dependency rules.

These dependency rules define the conditions (such as success, failure or completion-code) that can be used to start or stop a step or the chain itself.

Such a chain is a (named) series of programs that are linked together for a combined object. A chain is created with dbms_scheduler.create_chain.

Rules can be added to a chain with dbms_scheduler.define_chain_rule.

Attributes of a chain include

pause
paused_before
skip
restart_on_failure
restart_on_recovery
destination_name (See all_scheduler_external_dests and all_scheduler_db_dests)
credential_name

Chain steps

A chain consists of chain steps.

Chain rules

Chain rules specify when steps run and define the dependencies between steps.

A rule consists of

a condition (formulated in scheduler chain condition syntax or SQL syntax that is valid in a where clauses
an action

The action is executed when the condition evaluates to true.

Chain rules are evaluated

when the chain job starts
at the end of each step
at the end of the (optional) interval evaluation_interval (which is an attribute of a chain)

Possible actions include:

[AFTER delay_interval] START step_n [, step_m …] (delay_interval formatted as hh:mm:ss)
STOP step_n [, step_m …]
END [ { ret_val | step_name.errorcode } ] (which ends the chain)

Chain condition syntax

The chain condition syntax takes two forms:

stepname [NOT] {SUCCEEDED|FAILED|STOPPED|COMPLETED}
stepname ERROR_CODE {comparision_operator|[NOT] IN} {integer|list_of_integers}

The result of these expressions can be combined with and, or and not(…).

There are also the two literals true and false.

Note that the error_code is positive.

Starting and stopping a chain

In order for a chain to be able to be started when the chain job starts, the chain must have a rule that evaluates to true.

The chain stops when a rule with an end action evaluates to true.

Pausing and/or skipping

It's possible to temporarily pause an entire chain or some chain branches by setting the pause attribute to true (dbms_scheduler.alter_chain, dbms_scheduler.alter_running_chain).