Execute a shell script from a PL/SQL procedure

Oracle Database Tips by Donald BurlesonMarch 4, 2015

Question: I've been trying to get dbms_scheduler to run a UNIX shell script through dbms_scheduler.

Answer: Generally, OS-side activities are best managed in the OS environment (e.g. a crontab), but there are cases where you want to shell-out from Oracle and issue native OS commands, as is the case when you execute a shell script from PL/SQL.

You can invoke a shell script from inside PL/SQL and use dbms_scheduler to invoke shell scripts, or execute shell scripts with a variety of other ways inside PL/SQL. There are many ways to execute a server-side script from inside Oracle:

You can execute UNIX commands from inside Oracle by using the EXTPROC functionality.
You can define an external routine and use the dbms_pipe package to call a UNIX shell script.
Here is an example of a invoking a shell script from PL/SQL using dbms_scheduler:

DBMS_SCHEDULER.create_program
(
program_name => 'sch_program',
program_type => 'EXECUTABLE',
program_action => '/home/nobody/input/test_job.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Test Program'
);
end;
/

Bert Scalzo notes that when using dbms_job to execute an external routine, the "state" is lost, and variable must be fully-qualified. When you call DBMS_JOB, Oracle will spawn or fork a new process or thread to handle that job - and it does not inherit the current PL/SQL code's execution environment (i.e. variables).

Note: You must either fully resolve those variables before making the call like something this (note I did not compile this code, I'm just trying to throw you some concepts - so code may not be 100% accurate):

declare
v_job NUMBER;
v_id INTEGER;
ip_user VARCHAR2(100);
v_job_parm varchar2(100);
begin
v_id := 21;
ip_user := 'my_user_name';
v_job_parm := MY_PKG.sp_helper(v_id,ip_user);
DBMS_JOB.SUBMIT(v_job,v_job_parm);
end;

See how the value passed to the DBMS_JOB call has now been resolved before the call. The other method (if you must keep the MY_PCK call in the DBMS_JOBS call) would be more like this:

DBMS_JOB.SUBMIT(v_job,'MY_PKG.sp_helper('||v_id||','||ip_user||');');

For working examples of calling shell scripts from PL/SQL, get the book Linux for the Oracle DBA:

Hypercharge Oracle on Linux!

The landmark book "Linux for the Oracle DBA" is a complete guidebook of expert tips and secrets for automating Oracle database administration. It's packed with working Linux scripts and tools to make any DBA look like a guru.

��