Oracle Scheduler Job Chain

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

A chain is a named series of programs that are linked together for a combined objective. Each position within a chain of interdependent programs is referred to as a step. Each step can point to one of the following: a program, another chain (a nested chain), an event.

Note: This feature introduced in Oracle 10g release 2.

To create and use a chain:

1. Create a chain object

DBMS_SCHEDULER.CREATE_CHAIN (
CHAIN_NAME => 'bulk_load_chain',
RULE_SET_NAME => NULL,
EVALUATION_INTERVAL => NULL,
COMMENTS => 'Load data and run reports')

2. Define one or more chain steps. You define a step that points to a program or nested chain.

DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'do_bulk_load',
PROGRAM_NAME => 'hr.load_data_prog)

Also you can define a step that waits for an event to occur by using the DEFINE_CHAIN_EVENT_STEP procedure. Procedure arguments can point to an event schedule or can include an in-line queue specification and event condition.

DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'stop_when_disk_full_evt'
EVENT_SCHEDULE_NAME => 'disk_full_sched')
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'load_data_evt',
EVENT_CONDITION =>
'tab.user_data.object_owner=''HR'' and
tab.user_data.object_name = ''DATA.TXT'' and
tab.user_data.event_type =''FILE_ARRIVAL'' ',
QUEUE_SPEC => 'HR.LOAD_JOB_EVENT_Q')

3. Define chain rules. Each rule has a condition and an action.

If the condition evaluates to TRUE, the action is performed. Conditions are usually based on the outcome of one or more previous steps. A condition accepts Boolean and numeric integer values in an expression.

The entire expression must evaluate to a Boolean value.

The simplified syntax of a chain condition is as follows:

'factor|NOT(factor)[AND|OR factor]'

factor:

stepname ERROR_CODE number|[NOT]step_condition

When creating a rule condition using the simplified syntax:

� You specify one or more factors, and a Boolean operator (AND, OR, or NOT).

� A factor can be either a simple Boolean value (TRUE or FALSE) or a chain condition. A chain condition describes the condition of another step in the job chain. You can use the following to describe the chain condition:

o The current state of the chain step:

SUCCEEDED

FAILED

STOPPED

COMPLETED

o The error code returned by the chain step. The error is a numeric value, and can be:

Evaluated within a numeric clause

Compared to a list of values using an IN clause

You can use negative factors, by enclosing the factor in parentheses and prefixing the factor with the NOT operator.

Examples:

'step1 SUCCEEDED AND step2 ERROR_CODE = 3'
'TRUE'
'step3 NOT COMPLETED AND NOT (step1 SUCCEEDED)'
'step2 ERROR_CODE NOT IN (1,2,3)'

You can also refer to attributes of chain steps of the chain (this is called bind-variable syntax). The syntax is as follows:

STEP_NAME.ATTRIBUTE

� Possible attributes are: completed, state start_date, end_date, error_code, and duration.

� Possible values for the state attribute include:

'NOT_STARTED', 'SCHEDULED', 'RUNNING',
'PAUSED', 'SUCCEEDED', 'FAILED', and
'STOPPED'.

� If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE'; otherwise, completed is 'FALSE'.

Some examples of the bind variable syntax are:

':step1.state=''SUCCEEDED'' and
:step2.error_code=3'
'1=1'
':step3.state != ''COMPLETED'''
':step2.error_code not in (1,2,3)'
':step1.state = ''NOT_STARTED'''

The rule action specifies what is to be done as a result of the rule being triggered. A typical action is to run a specified step. Possible actions include:

o START step_1[,step_2...]
o STOP step_1[,step_2...]
o END [{end_value | step_name.error_code}]

When the job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. You can also configure rules to be evaluated at regular intervals by using the EVALUATION_INTERVAL attribute of the chain.

You add a rule to a chain with the DEFINE_CHAIN_RULE procedure:

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'TRUE', -- starting step
ACTION => 'START load_data_evt,
stop_when_disk_full_evt',
Rule_Name => 'dataload_rule1',
COMMENTS => 'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'load_data_evt COMPLETED',
ACTION => 'START do_bulk_load',
RULE_NAME => 'dataload_rule2');
END;

4. Enable a chain with the ENABLE procedure (A chain is always created disabled). Enabling an already enabled chain does not return an error.

DBMS_SCHEDULER.ENABLE ('bulk_load_chain');

5. To run a chain, you must create a job of type 'CHAIN'. The job action must refer to the chain name.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'bulk_load_chain_job',
job_type => 'CHAIN',
job_action => 'bulk_load_chain',
repeat_interval => 'freq=daily;byhour=7',
enabled => TRUE);
END;