Oracle Job Scheduler Guide With Examples – Part 3
This is the last article in our 3 article series we have written on Oracle Job Scheduler. In this article, we will see the oracle job scheduling example that will tell us how we can schedule a windows executable batch file.
Guide Index
- Oracle Job Scheduler Guide With Examples – Part I
- Oracle Job Scheduler Guide With Examples – Part II
- Oracle Job Scheduler Guide With Examples – Part III
Schedule an Executable (Batch file)
As mentioned earlier we can schedule an external executable file using the oracle scheduler. We have a simple java program that generates files. We have written a batch wrapper for that and we will schedule this batch file as one of our steps in the chain.
This is code we have in run-java-program.bat
|
1 2 3 4 5 6 |
@echo off SET location=%~dp0 cd %location% java WriteFile |
We will add one program RUNJAVA as 
Create one chain JAVATEST and add a single step as
Add start and end rule as shown below.
As we have only one step, once you are done our oracle job scheduler example will look like this in SQL Developer.
Now schedule a job running every 30 seconds as 
Important Note
If a job or chain is running an external program, we need to make sure that Oracle Schedule Service is up and running. If it’s down you will get errors in the execution of job or chain.
In the below screen its disabled by default. Before you schedule, make sure the below service is running.
Once the scheduling job is done, it will start running and files will start getting created as per your java file and batch file configuration. Below is the sample output.
Simple, right? I bet you won’t find this simple oracle scheduler job example anywhere else 🙂
Below I have listed some of the PL/SQL blocks or commands that will help you to drop Job, Program, Chain, etc.
Drop Job
|
1 2 3 4 5 6 |
BEGIN DBMS_SCHEDULER.DROP_JOB(job_name => '"PAVANS"."ORACLE_JOB_PROC"', defer => false, force => false); END; / |
Drop Program
|
1 2 3 4 5 |
BEGIN DBMS_SCHEDULER.DROP_PROGRAM(program_name => 'PAVANS.PROGRAM_PROC_STEP_1', force => false); END; / |
Drop ChainÂ
|
1 2 3 4 5 |
BEGIN DBMS_SCHEDULER.DROP_CHAIN(chain_name => 'PAVANS.ORACLE_JOB_CHAIN', force => false); END; / |
Drop Chain Step
|
1 2 3 4 5 6 7 8 |
BEGIN DBMS_SCHEDULER.DROP_CHAIN_STEP ( CHAIN_NAME => '"PAVANS"."ORACLE_JOB_CHAIN"', force => false, step_name => '"ORACLE_JOB_CHAIN_STEP_1"' ); END; / |
Drop Chain Rule
|
1 2 3 4 5 6 |
BEGIN DBMS_RULE_ADM.DROP_RULE( rule_name => '"ORACLE_JOB_CHAIN_RULE_2"', force => true); END; / |
Before we close our article let me share the tables we have used for our illustrations in the entire series.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE JOB_PARAMETERS ( PARAM_ID NUMBER(9) PRIMARY KEY, PARAM_NAME VARCHAR2(25), PARAM_VALUE VARCHAR2(500), DEFAULT_VALUE VARCHAR2(500), PARAM_STATUS VARCHAR2(25), JOB_MESSAGE VARCHAR2(1500) ); CREATE TABLE JOB_LOG ( LOG_ID NUMBER(9) PRIMARY KEY, LOG_MESSAGE VARCHAR2(25), LOG_TIME TIMESTAMP ); |
You can use the below query to get some details on the Jobs we have scheduled in the oracle schema
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT JOB_NAME, REPEAT_INTERVAL, LAST_START_DATE, LAST_RUN_DURATION, JOB_TYPE, PROGRAM_NAME, JOB_ACTION, STATE, JOB_PRIORITY, RUN_COUNT, MAX_RUNS, FAILURE_COUNT FROM USER_SCHEDULER_JOBS; |
That’s it. I guess we are good to conclude our 3 article series on Oracle Job Scheduler. Please feel free to get back to us if you have any queries.
You can refer to the Oracle package and tables scripts from our Git repository.
Download from GitYou may also be interested in our basic SQL Tutorials from scratch :Â
https://www.opencodez.com/software-testing/sql-for-qa-tutorial-select-query.htm
HI i wanted to schedule a event based scheduler , could you please help me out.
the job should be get executed when we update a flag in a table
Thanks you for nice tutorial.
Glad you found it useful !
keep visiting ! Keep learning !
It was very useful! Thank you for sharing
Thanks Bhupinder
Thanks for this clear tutorials. I have followed your tutorial from part 1 until part 3.
Appreciate and I am glad you found it useful.