Skip navigation.

12c Scheduler new feature: external_script jobs

In earlier releases, running a shell script from the Scheduler was awkward. Oracle has realized this, and introduced a new job type in 12c that makes it simple.

In release 11g, to run a shell script one had to create an external job that launches the command interpreter, and pass through the script as a command line argument. Yes, it worked - but it was bit awkward. For example:

exec dbms_scheduler.create_credential(-
credential_name=>'mycred',-
username=>'oracle',-
password=>'oracle')

exec dbms_scheduler.create_job(-
job_name=>'batchfile',-
job_type=>'executable',-
job_action=>'c:\windows\system32\cmd.exe',-
number_of_arguments => 2,-
enabled=>false,-
auto_drop=>false,-
credential_name=>'mycred')

exec dbms_scheduler.set_job_argument_value(-
job_name=>'batchfile',-
argument_position=>1,-
argument_value=>'/c')

exec dbms_scheduler.set_job_argument_value(-
job_name=>'batchfile',-
argument_position=>2,-
argument_value=>'c:\tmp\batchfile.bat')

exec dbms_scheduler.enable('batchfile')

There are any number of MOS articles where people bemoan the fact that simply running a shell script as the job_action doesn't work. Not a problem in 12c,where we have the external_script job type:
exec dbms_scheduler.create_job(-
job_name=>'myscript',-
job_type=>'external_script',-
job_action=>'c:\tmp\batchfile.bat',-
enabled=>true,-
auto_drop=>false,-
credential_name=>'mycred')

exec dbms_scheduler.run_job('myscript')

Isn't that a great improvement?

I've given code for Windows deliberately - because there is an extra problem you may face: the job fails, and in your trace file you find this,

ORA-27369: job of type EXECUTABLE failed with exit code: The storage control block address is invalid.

That message does not exist in MOS (not that I could find) but the problem is clear when you look at USER_SCHEDULER_JOB_RUN_DETAILS.OUTPUT:
C:\Windows\system32\cmd.exe execution failed: 1385, Logon failure: the user has not been granted the requested logon type at this computer.

You'll need to ask your System Administrator to sort out your Windows account, and then everything will be fine.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com