Executing oracle procedure using cronjob

Shell Programming and Scripting
1

Hi,

Below is the code to execute the procedure "dbms_job.broken" from the shell script.

on executing manually, it works properly without any error.

but it is not working when scheduled using the cronjob.

#!/usr/bin/bash

user_name="oracdb"
password="ora123"
tns="localdb"
ORACLE_BASE="/oracle"
ORACLE_HOME="/oracle/v9.0.1"
ORACLE_SID="mydb"
PATH="/oracle/v9.0.1/bin:/usr/bin:"

set `sqlplus -s $user_name/$password@$tns<<EOS
exec dbms_job.broken(118,TRUE);
exit;
EOS`

Why the above script is not working when executed using the cronjob ?

Regards

2

You are missing something in your environment varaibles, this will show what the problem is:

set `sqlplus -s $user_name/$password@$tns<<EOS > /full/path/to/my/directory/oracle.log
exec dbms_job.broken(118,TRUE);
exit;
EOS`

Read the log for the error. Why did you use set `command` if there is nothing else in the script? That is, instead of just a here doc. Plus the syntax is "set - `command` ", if I understand what you are doing.

3

The error messages may actually be in unix mail for the user who owns the crontab.

What is in the file: /full/path/to/my/directory/oracle.log