Running a Windows Batch file from DBMS_SCHEDULER

In an ideal world ….

Luton would have won the play-off final
I would have won the lottery by now
…and Oracle databases would run on Linux.

Out in the real world however, there are times when Oracle running on a Windows server is just unavoidable.
That’ll be the Real World with real data and real security issues, mixed in with – possibly – the real need to initiate a batch script from inside the database.
What follows are details of how to do this in Oracle 10g R2 running on a Windows server.

Before I go on there are three points that you should bear in mind here :

  1. things have changed in 11g. You should refer to the Oracle documentation here
  2. This is a potential security issue
  3. This is REALLY a potential security issue

OK, so I realise that points 2 and 3 are technically the same, but it’s such an important point that it’s worth mentioning twice !

One other point to mention – XE is a bit different. There are one or two bits missing from the installation which tend to make it a bit tricky to get this working on Oracle XE, especially on Linux.

Server setup

To run an External job, you need to enable and start the OracleJobScheduler Service on the database server.
It’s important to bear in mind that any batch scripts you run via the scheduler will run under the user account that starts this service.
Effectively, what you are doing by starting this service is giving every database user who has the CREATE EXTERNAL JOB privilege, privileges of the account starting this service on the server.
For this reason, using the Administrator account to start the service is probably not a great idea.

Database Privileges

With the service up and running on the server, the next step is to make sure that the user running the scheduled job has the CREATE JOB and CREATE EXTERNAL JOB privileges.

The batch script

For our purposes, we’re going to execute the following script using DBMS_SCHEDULER :

echo silly > x:\batch_scripts\output\silly.txt

The batch script is in a directory called x:\batch_scripts on the server. It will output the text “silly” to a text file in the output directory.
NOTE – when we actually come to execute this script, the current directory will be C:\Windows\System32.
This is why we need to specify the full path for the file the batch script is writing to.

The database job

After all that, we’re finally ready to create the job. At this point, there are a couple of things that might not be immediately apparent.
First off, you can’t call the batch script directly. Instead, you need to invoke cmd.exe.
Next is that you need to make sure that the job terminates on completion, to avoid the scheduler hanging around waiting for it.

BEGIN
	SYS.DBMS_SCHEDULER.CREATE_JOB( 
		job_name => 'silly_job',
        job_type => 'EXECUTABLE',
        job_action => 'C:\WINDOWS\system32\cmd.exe',
        repeat_interval => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=18;BYMINUTE=0;BYSECOND=0',
        start_date => SYSTIMESTAMP at time zone 'EUROPE/LONDON',
        job_class => 'DEFAULT_JOB_CLASS',
        comments => 'Job to test call out to batch script on Windows',
        auto_drop => FALSE,
        number_of_arguments => 3,
        enabled => FALSE);

	SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
        job_name => 'silly_job', argument_position => 1, argument_value => '/q'); 
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
        job_name => 'silly_job', argument_position => 2, argument_value => '/c'); 
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( 
        job_name => 'silly_job', argument_position => 3, argument_value => '"x:\batch_scripts\silly.bat"'); 
    SYS.DBMS_SCHEDULER.ENABLE( 'silly_job' ); 
END;
/

As you can see, the job_action is actually to run cmd.exe.
As for the arguments we’re passing in …

  • /q – is the cmd.exe switch to turn off echo
  • /c – is the cmd.exe switch to terminate after completing the specified command
  • .

  • ‘”x:\batch_scripts\silly.bat”‘ – the batch file we actually want to run

Now for a quick test….

BEGIN
	DBMS_SCHEDULER.RUN_JOB( 'silly_job');
END;
/

If all goes well you should see that the file silly.txt has been created on the server.
You should also see that the job status is SUCCEEDED :

SELECT *
FROM user_scheduler_job_log
WHERE job_name = 'SILLY_JOB'
/

Looking on the bright side, West Ham did manage to edge their play-off final…so I have all summer to build up my anticipation…for the coming
relegation dog-fight next season.

About these ads

7 thoughts on “Running a Windows Batch file from DBMS_SCHEDULER

    • Sean,
      the issue, or potential issue, is that by granting a database user the CREATE EXTERNAL JOB privilege, you implicitly give them the same privileges on the Database Server as the OS user who starts the OracleJobScheduler service. If this happens to be the Administrator user, you are then able to do anything you like on the server from the database account. Remember, you don’t have to execute a batch script, you can just pass in a DOS command to the job you create.
      This is less of an issue in 11g (see the link in the post).

      HTH

      Mike

    • Could you provide a bit more information ?
      The command that you used to created the job in the database and the location and contents of the batch script would be handy.

      Thanks,

      Mike

  1. Pingback: Tutorial #89: Execute batch file using Oracle Job Scheduler

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s