Calling Shell Scripts from Oracle 10gR2

The user requirement was to run a database export on demand from the click of a web browser button and they wanted to hide the nitty gritties of the database export except

Specifying the dump file name , ftp server name etc.

We decided to use the oracle 10g feature of invoking a shell script(which does all the above tasks) from dbms_scheduler built-in package ( which has the capability to call shell scripts and can even pass

command line arguments to them).

Here is the sample code for proof of concept .

contents of /tmp/junk.sh (shell script which takes in 1 argument)

+++++++++++++++++++++++++++++++++++++++++++++++++++

#!/bin/ksh

touch /tmp/$1

contents of the stored procedure which calls dbms_scheduler

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

create or replace procedure p1(abc varchar2)

is

begin

DBMS_SCHEDULER.CREATE_PROGRAM (

program_name => 'TEST123',

program_type => 'EXECUTABLE',

program_action => '/tmp/junk.sh',

number_of_arguments => 1,

enabled => FALSE,

comments => 'Test Shell Script'

);

DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (

program_name => 'TEST123',

argument_position => 1,

argument_type => 'VARCHAR2'

);

DBMS_SCHEDULER.create_job(job_name => 'TEST_JOB',

program_name => 'TEST123',

start_date => sysdate,

auto_drop => FALSE,

comments => 'testing 123');

dbms_scheduler.set_job_argument_value('TEST_JOB',1,abc);

DBMS_SCHEDULER.enable(NAME => 'TEST123');

DBMS_SCHEDULER.enable(NAME => 'TEST_JOB');

DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB', use_current_session => TRUE);

DBMS_SCHEDULER.drop_job(job_name => 'TEST_JOB');

DBMS_SCHEDULER.DROP_PROGRAM (program_name => 'TEST123');

commit;

end;

/

By executing procedure p1 , I was able to successfully run a shell script and also pass a argument to it.

The only hitch is the shell script is run as an OS user called nobody and to change it please read the following link

http://forums.oracle.com/forums/thread.jspa?messageID=1748530

Hope this was useful to you.