Execute batch file using Oracle Job Scheduler
Hey everyone!
The Oracle database includes a built-in scheduler that allows scheduling functions and procedures that can be called from any PL/SQL program. The DBMS_SCHEDULER package introduced in Oracle 10g helps to create, run and monitor jobs. In addition, one can also define a job to execute a batch (.bat) file.
In order to run an external job, you need to enable and start the OracleJobScheduler service on the database server. Through this tutorial, we will learn how to execute a batch file using Oracle Job Scheduler.
Pre-requisites: Oracle 11gR2 Enterprise Edition, Windows 7 (64 bit)
Step 1: Create a batch script
Let's begin by creating a batch script called example1.bat. On Windows, launch any text editor like Notepad and include the following contents.
example1.bat
echo test > F:\programs\calling_job_from_oracle\output\sample.txt
The above batch file will output the word "test" to a text file in the output directory. The batch file will automatically create the text file upon execution.
Step 2: Create Oracle job
Open SQLPlus and log in as a user with highest privileges. Now, let's create our job definition by executing the below commands in the console.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB( job_name => 'example_job',
job_type => 'EXECUTABLE',
job_action => 'C:\WINDOWS\system32\cmd.exe',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Job to call batch script on Windows',
auto_drop => FALSE,
number_of_arguments => 3,
enabled => FALSE);
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'example_job', argument_position => 1, argument_value => '/q');
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'example_job', argument_position => 2, argument_value => '/c');
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'example_job', argument_position => 3, argument_value => '"F:\programs\calling_job_from_oracle\example1.bat"');
SYS.DBMS_SCHEDULER.ENABLE( 'example_job' );
END;
/
As seen above, it is important to note that one can't directly execute a batch script. Instead, we need to open an instance of the command prompt and pass an argument in order to execute the batch script. For instance, /q is the cmd.exe switch to turn off echo where as /c is the switch to terminate the same after completing the specified command. Finally, you can test the job by executing the below PL/SQL statement.
BEGIN
DBMS_SCHEDULER.RUN_JOB( 'example_job');
END;
/
If the job is executed successfully, you should see a sample.txt file present in the output folder. That's it for this tutorial. Hope it helps! 😄
Reference:
