Running shell script from Oracle

It’s simple!!

Especially with 10g, it is even more simpler.

Here is an example:

Step 1: Create a shell script and grant execute priv to oracle user. I created a simple one:

bash-3.00$ cat /tmp/test.sh
#!/usr/bin/ksh
echo "Test succeeded `date`" >> /tmp/test.log

Note: Do not forget to start your shell script with the shell you are using.
>> #!/usr/bin/ksh should be present in your shell script otherwise, the job will fail with ORA-27369 job of type EXECUTABLE failed with

bash-3.00$ chmod 755 /tmp/test.sh

Step 2: Create the job:
Remember user doing the following should have “CREATE JOB” privilege.


SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => 'TEST_SHELL',
4 job_type => 'EXECUTABLE',
5 job_action => '/tmp/test.sh',
6 start_date => sysdate,
7 repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', /* every one hour */
8 enabled => TRUE,
9 comments => 'Testing shell script from Oracle');
10 END;
11 /

PL/SQL procedure successfully completed.

Step 3: Check if the job is running:

bash-3.00$ cat /tmp/test.log
Test succeeded Wed Jan 31 01:02:23 PST 2007
Test succeeded Wed Jan 31 01:03:23 PST 2007
Test succeeded Wed Jan 31 01:04:23 PST 2007