Oracle DBMS_SCHEDULER — Job Chain

An Oracle Job Chain is a feature of DBMS_SCHEDULER that organizes multiple executable steps into a single workflow.

A chain controls the execution order, dependencies, parallel processing, synchronization, and failure handling of its steps.

Unlike a regular Scheduler job, a Job Chain can execute several programs sequentially or concurrently and start subsequent operations based on the results of previous steps.

1. Steps to Create a Job Chain

1.1. Create the chain — CREATE_CHAIN

Creates the chain structure.

BEGIN
    DBMS_SCHEDULER.CREATE_CHAIN(
        chain_name => 'DWH_CHAIN',
        comments   => 'DWH processing chain'
    );
END;
/

1.2. Create programs — CREATE_PROGRAM

Defines the operations executed by chain steps.

BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
        program_name   => 'PRG_JOB_1',
        program_type   => 'STORED_PROCEDURE',
        program_action => 'PRC_JOB_1',
        enabled        => TRUE
    );
END;
/

The stored procedure PRC_JOB_1 must already exist.

1.3. Define chain steps — DEFINE_CHAIN_STEP

Associates Scheduler programs with chain steps.

BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
        chain_name   => 'DWH_CHAIN',
        step_name    => 'JOB_1',
        program_name => 'PRG_JOB_1'
    );
END;
/

1.4. Define chain rules — DEFINE_CHAIN_RULE

Defines when a step executes.

BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
        chain_name => 'DWH_CHAIN',
        condition  => 'TRUE',
        action     => 'START JOB_1',
        rule_name  => 'RULE_1'
    );
END;
/

1.5. Enable the chain — ENABLE

Activates the chain for execution.

BEGIN
    DBMS_SCHEDULER.ENABLE(
        name => 'DWH_CHAIN'
    );
END;
/

1.6. Create the chain job — CREATE_JOB

Creates a Scheduler job associated with the chain.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name   => 'JOB_DWH_CHAIN',
        job_type   => 'CHAIN',
        job_action => 'DWH_CHAIN',
        enabled    => TRUE,
        auto_drop  => FALSE
    );
END;
/

1.7. Execute the chain — RUN_JOB

Starts the chain.

BEGIN
    DBMS_SCHEDULER.RUN_JOB(
        job_name            => 'JOB_DWH_CHAIN',
        use_current_session => FALSE
    );
END;
/

1.8. Monitor execution

Displays currently running chain steps.

SELECT owner,
       job_name,
       job_subname,
       state
FROM   dba_scheduler_running_chains
WHERE  job_name = 'JOB_DWH_CHAIN';

2. Execution Types

2.1. Sequential execution

START
  |
  +--> JOB_1 --> SUCCEEDED
  |
  +--> JOB_2 --> SUCCEEDED
  |
  +--> JOB_3 --> RUNNING

Steps execute one after another, depending on the preceding step's result.

condition => 'TRUE',
action    => 'START JOB_1'

condition => 'JOB_1 SUCCEEDED',
action    => 'START JOB_2'

condition => 'JOB_2 SUCCEEDED',
action    => 'START JOB_3'

condition => 'JOB_3 SUCCEEDED',
action    => 'END'

Each condition/action pair represents a separate chain rule.

2.2. Parallel execution

START
  |
  +--> JOB_1 --> SUCCEEDED --+
  |                          |
  +--> JOB_2 --> SUCCEEDED --+--> END
  |                          |
  +--> JOB_3 --> SUCCEEDED --+

Multiple steps start independently and can execute concurrently.

condition => 'TRUE',
action    => 'START JOB_1'

condition => 'TRUE',
action    => 'START JOB_2'

condition => 'TRUE',
action    => 'START JOB_3'

condition => 'JOB_1 SUCCEEDED AND
              JOB_2 SUCCEEDED AND
              JOB_3 SUCCEEDED ',
action    => 'END'

2.3. Parallel execution with final calculation

START
  |
  +--> JOB_1 --> SUCCEEDED --+
  |                          |
  +--> JOB_2 --> SUCCEEDED --+--> FINAL_CALCULATION --> END
  |                          |
  +--> JOB_3 --> SUCCEEDED --+

Three steps execute concurrently. A final step starts after all three complete.

condition => 'JOB_1 SUCCEEDED AND
              JOB_2 SUCCEEDED AND
              JOB_3 SUCCEEDED ',
action    => 'START FINAL_CALCULATION'

condition => 'FINAL_CALCULATION SUCCEEDED',
action    => 'END'

2.4. Conditional execution and failure handling

START CHAIN
    |
    v
  JOB_1
    |
    +-- SUCCEEDED --> JOB_2
    |                   |
    |                   +-- SUCCEEDED --> JOB_3
    |                   |                   |
    |                   |                   +-- SUCCEEDED --> END SUCCESS
    |                   |                   |
    |                   |                   +-- FAILED ----> END ERROR 20003
    |                   |
    |                   +-- FAILED -----------------------> END ERROR 20002
    |
    +-- FAILED ----> ERROR_HANDLER
                         |
                         +-- SUCCEEDED --> END ERROR 20001
                         |
                         +-- FAILED ----> END ERROR 20004

Execution follows different paths depending on the step's result.

condition => 'JOB_1 SUCCEEDED',
action    => 'START JOB_2'

condition => 'JOB_1 FAILED',
action    => 'END 20001'

3. DEFINE_CHAIN_RULE Parameters

4. Condition Values

Logical operators include AND, OR, NOT, and parentheses.

5. Action Values

6. Main Scheduler Methods

7. Monitoring Views

Important: Chain steps execute according to rule conditions, not the order in which the rules were created. Parallel execution is achieved through independent start rules, without a dedicated parallelism parameter.