RUNNING SHELL SCRIPT IN ORACLE

RUNNING SHELL SCRIPT IN 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/kshecho "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.logTest succeeded Wed Jan 31 01:02:23 PST 2007Test succeeded Wed Jan 31 01:03:23 PST 2007Test succeeded Wed Jan 31 01:04:23 PST 2007