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 :
- things have changed in 11g. You should refer to the Oracle documentation here
- This is a potential security issue
- 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.
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.
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.