ORACLE 12C –  run external script

In earlier releases  running a shell script from the scheduler was difficult.
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.
In release 12c Oracle has improved the DBMS_SCHEDULER package functionality adding some interesting new features. One of this new features is the capability to run external scripts as part of our job action or even better the capability to write our own custom scripts and run them by invoking the shell interpreter (in case of unix environments) or the command prompt (in windows environments). This custom script can be directly writed inside the job definition.
This new job type is named ‘EXTERNAL_SCRIPT’. This definition specifies that the job is an external script that uses the command shell of the computer running the job.
The external_script job type:


  1.   script_file    varchar2(32000) ;/
  2.   BEGIN
  3.  script_file := ‘#!/bin/bash
  4. export PATH=$PATH:/bin
  5. cd /tmp
  6. mkdir external
  7. cd external
  8. touch prova.txt’;
  9.  DBMS_SCHEDULER.create_job(
  10. job_name     => ‘EXTJOB’,
  11.   job_type     => ‘EXTERNAL_SCRIPT’,
  12.   job_action     => script_file,
  13.    credential_name => ‘oracle_os_credentials’,
  14.    enabled     => TRUE);
  15.  END;
  16. /

PL/SQL procedure successfully completed.
On the database verify execution result:
SQL> SELECT job_name,

  •     status,
  • error#,
  • to_char(log_date,’yyyy-mm-dd hh24:mi’) log_date
  •  FROM user_scheduler_job_run_details
  •   ORDER BY 1;

————— ——————- ———–    ———————
EXTJOB        SUCCEEDED 0                   2015-06-08 14:28
On the operation system verify execution result:
[oracle@ora12c ~]$ cd /tmp/external
[oracle@ora12c external]$ ls -l
total 0
-rw-rw-rw- 1 oracle oinstall   Jun 08 14:28 prova.txt
[oracle@ora12c external]$

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *