Oracle Job Scheduler Guide With Examples – Part 2

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 – 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.

1. Right-click on the Programs in SQLDeveloper and click on New Program
2. You will see the screen as below
3. Provide the name and description of your program.
4. From drop-down select Stored Procedure as an option. It will list down all the procedures available.
5. Choose the one you desire to schedule.
6. If your procedure expecting any parameters, then you will have to specify the default values in the area available.
7. Click on Apply. The program is created and will be visible in SQL developer as
8. 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
9. 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.

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

Oracle Job Scheduler – Chain

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

1. 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.
2. 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.

1. In SQL Developer right-click on chains and click on New Chain.
2. You will see the screen below, provide a name for your chain.
3. 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”
4. Once in write mode, again right-click and choose Add Step. Below screen will appear  
5. Chain Name will be populated by default. Provide a step name.
6. Choose the Program from the drop-down available below. All programs you have configured in the earlier sections will appear. Choose one from them
7. Click Apply. The step will be created and will be available in the chain area to see.
8. Similarly, add other steps for the chain.
9. To add a rule, right-click on the chain area and choose Add Rule option.
10. Give a rule name
11. If you are adding a rule for the first step to start, then the condition has to be TRUE always.
12. In action, you need to specify START <STEP NAME>
13. We will start the next step only when the previous step is completed. So condition would be <PREVIOUS STEP NAME> COMPLETED
14. And action would be to START <NEXT STEP NAME>
15. To complete the execution of chain, we need to add END rule. That can be done as 
16. Condition = <LAST STEP NAME> COMPLETED and Action = END
17. Once all the steps and rules are added you will see something like below screen. This will give an idea about chain execution order.
18. Our chain is created. We will now schedule it for running using Job 
19. As you can see we need to specify chain as our execution and select available chain from the dropdown.
20. 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

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

1. Oracle Job Scheduler Guide With Examples – Part I
2. Oracle Job Scheduler Guide With Examples – Part II
3. Oracle Job Scheduler Guide With Examples – 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 – Select Query