Oracle Job Scheduler Guide With Examples – Part II

In our earlier tutorial we have seen some basic information on Oracle Job Scheduler. 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 chain. 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.

  1. Right click on the Programs in SQLDeveloper and click on New Program
  2. You will see screen as below
  3. Provide the name and description for your program.
  4. From drop down select Stored Procedure as 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 above section of creating a job with procedure. Only difference is that you have to choose Named Program in the drop down instead of Stored Procedure. As 
  9. All other steps to choose frequency, priority will remain same as we saw in previous article

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

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

Oracle Job Scheduler – Chain

Chains are 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 about 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 are step by step guide to create chain, step and step rules.

  1. In SQL Developer right click on chains and click on New Chain.
  2. You will see 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 step name.
  6. Choose Program from the drop down available below. All programs you have configured in earlier section 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 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 next step only when 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 drop down.
  20. 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 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 a windows executable as job. It will also list down some ddls to drop the chain, rule and other objects.

Please stay tuned for upcoming article. Meantime, feel free to get back to us for any queries.

Add a Comment

Your email address will not be published. Required fields are marked *