Using Chains

Chain Tasks and Their Procedures

Table 27-9 illustrates common tasks involving chains and the procedures associated with them.

Creating Chains

You create a chain by using the CREATE_CHAIN procedure. After creating the chain object with CREATE_CHAIN, you define chain steps and chain rules separately.

The rule_set_name and evaluation_interval arguments are normally left NULL. evaluation_interval can define the times that chain rules get evaluated, other than when the job starts or a step completes. rule_set_name is for advanced users only.

See Oracle Database PL/SQL Packages and Types Reference for more information on CREATE_CHAIN.

The following is an example of creating a chain:

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name          => 'my_chain1',
   rule_set_name       => NULL,
   evaluation_interval => NULL,
   comments            => 'My first chain');
END;
/

Defining Chain Steps

After creating a chain object, you define one or more chain steps. Each step can point to one of the following:

• A program

• Another chain (a nested chain)

• An event

You define a step that points to a program or nested chain by using the DEFINE_CHAIN_STEP procedure. An example is the following, which adds two steps to my_chain1:

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step1',
   program_name    =>  'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step2',
   program_name    =>  'my_chain2');
END;
/

To define a step that waits for an event to occur, you use the DEFINE_CHAIN_EVENT_STEP procedure. Procedure arguments can point to an event schedule or can include an inline queue specification and event condition. This example creates a third chain step that waits for the event specified in the named event schedule:

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
   chain_name           =>  'my_chain1',
   step_name            =>  'my_step3',
   event_schedule_name  =>  'my_event_schedule');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DEFINE_CHAIN_STEP and DEFINE_CHAIN_EVENT_STEP procedures.

Adding Rules to a Chain

Chain rules define when steps run, and define dependencies between steps. Each rule has a condition and an action. If the condition evaluates to TRUE, the action is performed. The condition can contain Scheduler chain condition syntax or any syntax that is valid in a SQL WHERE clause. The syntax can include references to attributes of any chain step, including step completion status. A typical action is to run a specified step.

Conditions are usually based on the outcome of one or more previous steps. For example, you might want one step to run if the two previous steps succeeded, and another to run if either of the two previous steps failed.

All rules added to a chain work together to define the overall behavior of the chain. 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 cause rules to be evaluated at regular intervals also. See "Creating Chains" for details.)

You add a rule to a chain with the DEFINE_CHAIN_RULE procedure. You call this procedure once for each rule that you want to add to the chain.

Starting the Chain

At least one rule must have a condition that always evaluates to TRUE so that the chain can start when the job starts. The easiest way to accomplish this is to just set the condition to 'TRUE' if you are using Schedule chain condition syntax, or '1=1' if you are using SQL syntax.

Ending the Chain

At least one chain rule must contain an action of 'END'. A chain job does not complete until one of the rules containing the END action evaluates to TRUE. Several different rules with different END actions are common, some with error codes, and some without.

If a chain has no more running steps or it is not waiting for an event to occur, and no rules containing the END action evaluate to TRUE (or there are no rules with the END action), the job enters the CHAIN_STALLED state. See "Handling Stalled Chains" for more information.

Example

The following example defines a rule that starts the chain at step 1 and a rule that starts step 2 when step 1 completes. rule_name and comments are optional and default to NULL.

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   condition    =>   'TRUE',
   action       =>   'START step1',
   rule_name    =>   'my_rule1',
   comments     =>   'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   condition    =>   'step1 completed',
   action       =>   'START step2',
   rule_name    =>   'my_rule2');
END;
/

Enabling Chains

You enable a chain with the ENABLE procedure. A chain must be enabled before it can be run by a job. Enabling an already enabled chain does not return an error.

The following example enables chain my_chain1:

BEGIN
DBMS_SCHEDULER.ENABLE ('my_chain1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the ENABLE procedure.

Creating Jobs for Chains

To run a chain, you must either use the RUN_CHAIN procedure or create a job of type 'CHAIN'. The job action must refer to the chain name, as shown in the following example:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name        => 'chain_job_1',
   job_type        => 'CHAIN',
   job_action      => 'my_chain1',
   repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',
   enabled         => TRUE);
END;
/

For every step of a chain job that is running, the Scheduler creates a step job with the same job name and owner as the chain job. Each step job additionally has a job subname to uniquely identify it. The job subname is included as a column in the views *_SCHEDULER_RUNNING_JOBS, *_SCHEDULER_JOB_LOG, and *_SCHEDULER_JOB_RUN_DETAILS. The job subname is normally the same as the step name except in the following cases:

• For nested chains, the current step name may have already been used as a job subname. In this case, the Scheduler appends '_N' to the step name, where N is an integer that results in a unique job subname.

• If there is a failure when creating a step job, the Scheduler logs a FAILED entry in the job log views (*_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS) with the job subname set to 'step_name_0'.

Dropping Chains

You drop a chain, including its steps and rules, by using the DROP_CHAIN procedure. An example of dropping a chain is the following, which drops my_chain1:

BEGIN
DBMS_SCHEDULER.DROP_CHAIN (
   chain_name   => 'my_chain1',
   force        => TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN procedure.

Running Chains

You can use the RUN_CHAIN procedure to run a chain immediately, without having to create a job ahead of time for the chain. You can also use RUN_CHAIN to run only part of a chain.

RUN_CHAIN creates a temporary job to run the specified chain. If you supply a job name, the job is created with that name, otherwise a default job name is assigned.

If you supply a list of start steps, only those steps are started when the chain begins running. (Steps that would normally have started do not run if they are not in the list.) If no list of start steps is given, the chain starts normally—that is, an initial evaluation is done to see which steps to start running. An example is the following, which immediately runs the chain my_chain1:

BEGIN
DBMS_SCHEDULER.RUN_CHAIN (
   chain_name    =>  'my_chain1',
   job_name      =>  'quick_chain_job',
   start_steps   =>  'my_step1, my_step2');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the RUN_CHAIN procedure.

Dropping Rules from a Chain

You drop a rule from a chain by using the DROP_CHAIN_RULE procedure. An example is the following, which drops my_rule1:

BEGIN
DBMS_SCHEDULER.DROP_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   rule_name    =>   'my_rule1',
   force        =>   TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN_RULE procedure.

Disabling Chains

You disable a chain by using the DISABLE procedure. An example is the following, which disables my_chain1:

BEGIN
DBMS_SCHEDULER.DISABLE ('my_chain1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DISABLE procedure.

Dropping Chain Steps

You drop a step from a chain by using the DROP_CHAIN_STEP procedure. An example is the following, which drops my_step2 from my_chain2:

BEGIN
DBMS_SCHEDULER.DROP_CHAIN_STEP (
   chain_name   =>   'my_chain2',
   step_name    =>   'my_step2',
   force        =>    TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN_STEP procedure.

Altering Chain Steps

You alter the SKIP, PAUSE, or RESTART_ON_RECOVERY attributes of a chain step by using the ALTER_CHAIN procedure. An example is the following, which causes my_step3 to be skipped:

BEGIN
DBMS_SCHEDULE.ALTER_CHAIN (
   chain_name  =>  'my_chain1',
   step_name   =>  'my_step3',
   attribute   =>  'SKIP',
   value       =>  TRUE);
END;
/

The ALTER_CHAIN procedure affects only future runs of the specified steps.

You alter the steps in a running chain by using the ALTER_RUNNING_CHAIN procedure. An example is the following, which causes step my_step1 to pause after it has completed—that is, its state is changed to PAUSED and its completed attribute remains FALSE:

BEGIN
DBMS_SCHEDULER.ALTER_RUNNING_CHAIN (
   job_name     =>   'my_job1',
   step_name    =>   'my_step1',
   attribute    =>   'PAUSE',
   value        =>    TRUE);
END;
/

The ALTER_RUNNING_CHAIN procedure affects only the running instance of the chain.

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the ALTER_CHAIN procedure.

Handling Stalled Chains

A chain can become stalled when no steps are running, no steps are scheduled to run, no event steps are waiting for an event, and the evaluation_interval for the chain is NULL. The chain can make no further progress unless you manually intervene. In this case, the state of the job that is running the chain is set to CHAIN_STALLED. (However, the job is still listed in the *_SCHEDULER_RUNNING_JOBS views.)

You can troubleshoot a stalled chain with the views ALL_SCHEDULER_RUNNING_CHAINS, which shows the state of all steps in the chain (including any nested chains), and ALL_SCHEDULER_CHAIN_RULES, which contains all the chain rules.

You can enable the chain to continue by altering the state of one of its steps with the ALTER_RUNNING_CHAIN procedure. For example, if step 11 is waiting for step 9 to succeed before it can start, and if it makes sense to do so, you can set the state of step 9 to 'SUCCEEDED'.

Alternatively, if one or more rules are incorrect, you can use the DEFINE_CHAIN_RULE procedure to replace them (using the same rule names), or to create new rules. The new and updated rules apply to the running chain and all future chain runs. After adding or updating rules, you must run EVALUATE_RUNNING_CHAIN on the stalled chain job to trigger any required actions.