Oracle DBMS_SCHEDULER Part 3 – Job Chains

A job chain is a series of jobs chained together via dependencies. Today’s post walks through creating a 3 job chain.

The first step is to create our programs. I described creating dbms_scheduler programs in the previous entry, Oracle DBMS SCHEDULER Part 2 – The API.

These are very simple programs with no arguments. You can use SQL Developer to create these or just write the SQL yourself.

This is the SQL I used:

BEGIN
    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM1',
        program_action => 'SCOTT.PROCEDURE1',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM1');    

    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM2',
        program_action => 'SCOTT.PROCEDURE2',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM2');    

    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM3',
        program_action => 'SCOTT.PROCEDURE3',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM3');
END;
/

Once the programs are created, I create the chain itself. Creating the chain is a simple command. You can right click on the SQL Developer chain node and select New Chain. There are only a few values to enter.

Here is the PL/SQL create code:

BEGIN
  DBMS_SCHEDULER.RUN_CHAIN (
    chain_name   => 'chain1',
    start_steps  => null );
END;
/

This is where SQL Developer gets really nice. The graphic support for job chains really makes visualizing the dependencies simple. Double click on the chain name or right click on it and select Open. Once the GUI window opens, right click and select Edit Mode.

Now that you are in edit mode, you can right click and select Create Step.

Enter the information as I did. The code that is created is:

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'SCOTT.chain1',
        STEP_NAME  => 'step1',
        PROGRAM_NAME => 'PROGRAM1'  );
END;
/

You should now have a GUI screen that looks something like this image:

Repeat the create step for the remaining two programs. The code generated for the final two steps should look like the code below:

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'SCOTT.chain1',
        STEP_NAME  => 'step2',
        PROGRAM_NAME => 'PROGRAM2'  );
END;
/
BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'SCOTT.chain1',
        STEP_NAME  => 'step3',
        PROGRAM_NAME => 'PROGRAM3'  );
END;
/

Think of the steps as jobs within the chain. The difference being that you won?t schedule the individual steps. You will schedule the chain instead.

Now that all of the steps are created, you have to create the rules that tie them together. A chain also begin with a START step and always ends with an END step. The begin always exists and the end is created by your rules.

Bring up the Create Rule screen by right clicking in the chain GUI window and selecting New Rule.

The START rule should always be unconditional. A 1=1 rule basically. Like this:

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => '"SCOTT"."CHAIN1"',
        rule_name  => 'geturdun',
        condition => 'TRUE',
        action => 'START step1'
        );
END;
/

Notice that as you add rules, the GUI windows ties the steps together with an arrow line. Continue adding the rules.

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'SCOTT.chain1',
        rule_name  => 'step1_to_step2',
        condition => 'STEP1 SUCCEEDED',
        action => 'START STEP2'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'SCOTT.chain1',
        rule_name  => 'step_2_to_step3',
        condition => 'STEP2 SUCCEEDED',
        action => 'START step3'
        );
END;
/

And then you create the end rule. This tells the scheduler that the job is actually finished. If you don’t add an end rule, the chain will get to the last step and enter a stalled state. You must also explicitly handle exceptions if you code the rules with STEPx SUCCEDDED. You can code the rules with STEPx COMPLETED and it will go on to the next step even in the case of an error.

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'SCOTT.chain1',
        rule_name  => 'finished',
        condition => 'STEP3 SUCCEEDED',
        action => 'END'
        );
END;
/

If you’ve followed along in SQL Developer, you should now have a GUI with the steps all tied together.

If you click on a step, SQL Developer will show you how it is defined.

You can view rules in the same way.

To runa chain, you use the DBMS_SCHEDULER.RUN_CHAIN procedure. You can enter the steps you want to start, or leave it null to start at the beginning.

BEGIN
  DBMS_SCHEDULER.RUN_CHAIN (
    chain_name   => 'chain1',
    start_steps  => null );
END;
/

The complete code follows:

BEGIN
    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM1',
        program_action => 'SCOTT.PROCEDURE1',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM1');    

    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM2',
        program_action => 'SCOTT.PROCEDURE2',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM2');    

    sys.dbms_scheduler.create_program(
        program_name => 'SCOTT.PROGRAM3',
        program_action => 'SCOTT.PROCEDURE3',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'SCOTT.PROGRAM3');
END;
/
BEGIN
    sys.dbms_scheduler.create_chain(
        comments => 'just a chain',
        chain_name => 'SCOTT.chain1'
    );
      sys.dbms_scheduler.enable(name=>'SCOTT.chain1');
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'SCOTT.chain1',
        STEP_NAME  => 'step1',
        PROGRAM_NAME => 'PROGRAM1'  );
END;
/
BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'SCOTT.chain1',
        STEP_NAME  => 'step2',
        PROGRAM_NAME => 'PROGRAM2'  );
END;
/
BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'SCOTT.chain1',
        STEP_NAME  => 'step3',
        PROGRAM_NAME => 'PROGRAM3'  );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => '"SCOTT"."CHAIN1"',

        rule_name  => 'geturdun',
        condition => 'TRUE',
        action => 'START step1'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'SCOTT.chain1',

        rule_name  => 'step1_to_step2',
        condition => 'STEP1 SUCCEEDED',
        action => 'START STEP2'
        );
END;
/
BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'SCOTT.chain1',

        rule_name  => 'step_2_to_step3',
        condition => 'STEP2 SUCCEEDED',
        action => 'START step3'
        );
END;
/
BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'SCOTT.chain1',

        rule_name  => 'finished',
        condition => 'STEP3 SUCCEEDED',
        action => 'END'
        );
END;
/