Oracle Job Scheduler Guide With Examples – Part 2

Pavan

8 years ago

In our earlier tutorial, we have seen some basic information on Oracle Job Scheduling. We have seen what are the core components like Job, Program, Chain etc.

In this article, we will see how oracle job scheduler can be used to define programs and chains. Just for recap, A program is a collection of metadata about a particular task. The programs are not schedulable on their own. Program can be created using PL/SQL Block, Stored Procedure etc.

A chain is a series of programs that are linked together to perform a particular group of activities. To create a chain, you need chain steps and rules to link each step to one another.

Oracle Job Scheduler x Program

As mentioned earlier, programs are not schedulable on their own. Here we will create a program with stored procedure and schedule that program using a job. i.e scheduling jobs.

Right-click on the Programs in SQLDeveloper and click on New Program
You will see the screen as below
Provide the name and description of your program.
From drop-down select Stored Procedure as an option. It will list down all the procedures available.
Choose the one you desire to schedule.
If your procedure expecting any parameters, then you will have to specify the default values in the area available.
Click on Apply. The program is created and will be visible in SQL developer as
To schedule a program, you will have to follow the steps that are mentioned in the above section of creating a database job with a procedure. The only difference is that you have to choose Named Program in the drop-down instead of the Stored Procedure. As
All other steps to choose the frequency, priority will remain the same as we saw in previous article

If you want to use direct PL/SQL way, you can use the below code.

BEGIN
    DBMS_SCHEDULER.create_program(
        program_name          =x 'PAVANS.PROGRAM_PROC_STEP_2',
        program_action        =x 'PAVANS.PKG_TEST_SCHEDULER.JOB_PROC_STEP_2',
        program_type          =x 'STORED_PROCEDURE',
        number_of_arguments   =x 0,
        comments              =x 'Program_Proc_Step_2',
        enabled               =x FALSE
    );

    DBMS_SCHEDULER.ENABLE(
        name   =x 'PAVANS.PROGRAM_PROC_STEP_2'
    );
END;

Execution output of this program can be seen in the job log tableÂ

Oracle Job Scheduler x Chain

Chains are a group of jobs combined together to achieve a common goal. A chain has 2 components

Step â Itâs the unit that will hold information about code to be executed. You can use only Program, or other chains as one of the steps.
Step Rule â These are the configuration of how steps are connected together and executed. You have to provide the action for step rule and condition on which the action will be performed.

Below is a step by step guide to create a chain and step by step rules.

In SQL Developer right-click on chains and click on New Chain.
You will see the screen below, provide a name for your chain.
Once chain is created open it in SQL developer. Right-click on the blank screen you see and click on to âSwitch to Write Modeâ
Once in write mode, again right-click and choose Add Step. Below screen will appear Â
Chain Name will be populated by default. Provide a step name.
Choose the Program from the drop-down available below. All programs you have configured in the earlier sections will appear. Choose one from them
Click Apply. The step will be created and will be available in the chain area to see.
Similarly, add other steps for the chain.
To add a rule, right-click on the chain area and choose Add Rule option.
Give a rule name
If you are adding a rule for the first step to start, then the condition has to be TRUE always.
In action, you need to specify START xSTEP NAMEx
We will start the next step only when the previous step is completed. So condition would be xPREVIOUS STEP NAMEx COMPLETED
And action would be to START xNEXT STEP NAMEx
To complete the execution of chain, we need to add END rule. That can be done asÂ
Condition = xLAST STEP NAMEx COMPLETED and Action = END
Once all the steps and rules are added you will see something like below screen. This will give an idea about chain execution order.
Our chain is created. We will now schedule it for running using JobÂ
As you can see we need to specify chain as our execution and select available chain from the dropdown.
The rest of the steps will be similar to Oracle Job Scheduler earlier section.

Once scheduled and started you can see the execution log for our chain in table JOB_LOG

PL/SQL code to create chain, step and rules can be referred as below

BEGIN
    DBMS_SCHEDULER.create_chain(
        comments     =x 'Oracle_Job_Chain',
        chain_name   =x 'PAVANS.ORACLE_JOB_CHAIN'
    );
    DBMS_SCHEDULER.enable(
        name   =x 'PAVANS.ORACLE_JOB_CHAIN'
    );
END;

BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
        CHAIN_NAME     =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        STEP_NAME      =x '"ORACLE_JOB_CHAIN_STEP_1"',
        PROGRAM_NAME   =x '"PAVANS"."PROGRAM_PROC_STEP_1"'
    );
    DBMS_SCHEDULER.ALTER_CHAIN(
        CHAIN_NAME   =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        STEP_NAME    =x '"ORACLE_JOB_CHAIN_STEP_1"',
        ATTRIBUTE    =x 'PAUSE',
        VALUE        =x false
    );

    DBMS_SCHEDULER.ALTER_CHAIN(
        CHAIN_NAME   =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        STEP_NAME    =x '"ORACLE_JOB_CHAIN_STEP_1"',
        ATTRIBUTE    =x 'SKIP',
        VALUE        =x false
    );

    DBMS_SCHEDULER.ALTER_CHAIN(
        CHAIN_NAME   =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        STEP_NAME    =x '"ORACLE_JOB_CHAIN_STEP_1"',
        ATTRIBUTE    =x 'RESTART_ON_FAILURE',
        VALUE        =x false
    );

    DBMS_SCHEDULER.ALTER_CHAIN(
        CHAIN_NAME   =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        STEP_NAME    =x '"ORACLE_JOB_CHAIN_STEP_1"',
        ATTRIBUTE    =x 'RESTART_ON_RECOVERY',
        VALUE        =x false
    );

END;
DBMS_SCHEDULER.define_chain_rule(
    chain_name   =x '"PAVANS"."ORACLE_JOB_CHAIN"',
    condition    =x 'TRUE',
    action       =x 'START "ORACLE_JOB_CHAIN_STEP_1"',
    rule_name    =x 'ORACLE_JOB_CHAIN_RULE_1',
    comments     =x 'First link in the chain.'
);
BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        comments =x 'First link in the chain.',       
        rule_name  =x '"ORACLE_JOB_CHAIN_RULE_1"',       
        condition =x 'TRUE',
        action =x 'START "ORACLE_JOB_CHAIN_STEP_1"'
        );   
END;
BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        comments =x 'Second step in the chain',       
        rule_name  =x '"ORACLE_JOB_CHAIN_RULE_2"',       
        condition =x '"ORACLE_JOB_CHAIN_STEP_1" COMPLETED',
        action =x 'START "ORACLE_JOB_CHAIN_STEP_2"'
        );   
END;
BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        comments =x 'End of Chain',       
        rule_name  =x '"ORACLE_JOB_CHAIN_RULE_4"',       
        condition =x '"ORACLE_JOB_CHAIN_STEP_3" COMPLETED',
        action =x 'END'
        );   
END;

You can see complete scheduler jobs, programs, and chains in SQL developer.

Well, this is pretty extensive information in one article, so we have decided to add one more article in this series.

The last article will show you how you can schedule windows executable as a job. It will also list down some DDLs to drop the chain, rule, and other objects.

Guide Index

Oracle Job Scheduler Guide With Examples x Part I
Oracle Job Scheduler Guide With Examples x Part II
Oracle Job Scheduler Guide With Examples x Part III

You can refer theÂ OracleÂ package and table script from out Git Repository.

Download from Git

You may also be interested in our basic SQL Tutorials from scratch :Â

SQL For QA : Tutorial#1 x Select Query