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

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

@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 itxs 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 wonxt 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

BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name =x '"PAVANS"."ORACLE_JOB_PROC"',
                                defer =x false,
                                force =x false);
END;
/

Drop Program

BEGIN
    DBMS_SCHEDULER.DROP_PROGRAM(program_name =x 'PAVANS.PROGRAM_PROC_STEP_1',
                                force =x false);
END;
/

Drop Chain 

BEGIN
    DBMS_SCHEDULER.DROP_CHAIN(chain_name =x 'PAVANS.ORACLE_JOB_CHAIN',
                                force =x false);
END;
/

Drop Chain Step

BEGIN
    DBMS_SCHEDULER.DROP_CHAIN_STEP  (
        CHAIN_NAME  =x '"PAVANS"."ORACLE_JOB_CHAIN"',
        force =x false,       
        step_name =x '"ORACLE_JOB_CHAIN_STEP_1"'
        );   
END;
/

Drop Chain Rule

BEGIN
  DBMS_RULE_ADM.DROP_RULE(
    rule_name =x '"ORACLE_JOB_CHAIN_RULE_2"', 
    force     =x true);
END;
/

Before we close our article let me share the tables we have used for our illustrations in the entire series.

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

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;

Thatxs 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 Git

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

https://www.opencodez.com/software-testing/sql-for-qa-tutorial-select-query.htm