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
1 comment:
You probably have the requirement to run a shell script inline with your PL/SQL logic, and have your PL/SQL code take an appropriate course of action based on the return code of the program you ran. This is not possible through using the dbms_scheduler.
I did a small writeup on how to setup a really simple Oracle External procedure to run host commands on the server. I think this will help you. Go to the following URL to read it:
http://timarcher.com/?q=node/9
I hope it helps you!
Post a Comment