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.

		job_name => 'silly_job',
        job_type => 'EXECUTABLE',
        job_action => 'C:\WINDOWS\system32\cmd.exe',
        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);

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

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….

	DBMS_SCHEDULER.RUN_JOB( 'silly_job');

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 :

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.

10 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).



    • 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.



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

  2. Hi Mike – firstly, apologies for being so late to the party. You mention that this is likely to be a pain to get working with XE running on Linux and sadly I’m trying to do just that. Having smacked my head against the proverbial brick wall for a while, I’m close to giving up!

    Just wondered if you could share any insight since I keep hitting:
    ORA-27370: job slave failed to launch a job of type EXECUTABLE
    ORA-27300: OS system dependent operation:accessing login executable failed with status: 2
    ORA-27301: OS failure message: No such file or directory
    ORA-27302: failure occurred at: sjseccel 1

    I’ve tried using a newly-created DBMS_SCHEDULER credential to run the job with the same result, and I’ve occasionally read glimpses of madness about copying files missing from XE using an EE environment – something I don’t have access to. Oh, and I’ve also read that the “oracle” user must exist (and have a primary group of “dba”) *prior to* the XE install, which I’ve also tried to no avail….

    Any ideas would be greatly appreciated!
    Many thanks, Jon.

    • Jon,

      Unfortunately the file copying madness is what is required to get this working in Express Edition.
      Both the 10g and 11g versions are missing a file called jssu.exe.

      If your main goal is to execute a shell script on the OS, then there is an alternative method that does work in XE.
      This involves defining a Pre-Processer on an External Table.
      You can find details of how this works here.
      Note that, as with the DBMS_SCHEDULER solution, you need to be a bit careful in terms of security when implementing this.

      Alternatively, if your main goal is to implement this functionality, then one other option may be to run a full-fat Oracle Enterprise Edition Database inside a VM.
      If your machine has 4GB RAM or more, then you could comfortably do this using an Oracle Developer Day Image.
      You can see details of how to install this here.



      • Thanks a lot for this Mike. I hadn’t heard of the external table pre-processor so I’ll take a look at that (unfortunately for the moment I’m stuck with XE unless I can persuade the relevant people in these parts that it’s worth their while upgrading to something like EE).

        Or perhaps it’ll be fixed in the next XE release…. Fingers crossed, and thanks again!



Leave a Reply

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

You are commenting using your 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