I have created two scripts to call SQL scripts to do some work. The scripts was successfully executed many times by manual. When I scheduled two scripts in crontab, I gave all necessary parameters. It could start, but couldn't run through. The log file didn't give enough error info. Anyone can help me to figure this out. What is the reason? Thanks.

1. crontab setting

0 4 * * 1-5 /app/xxxxx/xxxxx/scripts.ksh oracle_sid

2. error message in log file:

02/15/07 04:00:00: Database xxxprd.world is up and running.

02/15/07 04:00:00: Counting extraction records script started.

02/15/07 04:00:00: Error counting validation/xxxxxx_act.sql.
02/15/07 04:00:01: Exiting script.

Usually when a script works manually but not from Cron it is because of the environment. Cron runs a basic bourne shell and won't have the path, aliases, environment variables, etc. that you will when you run it manually. I can't tell if that is your problem here but check it out. Make sure your script sets all needed variables and paths at the beginning of it.

You are right. I do think it is environment setting. But I need to figure out which variable or path need to set.

Here are the env variables I used

#Initialize variables.
ORACLE_SID=$1
ORACLE_HOME=/oracle/product/9.2.0
WORK_DIR=`dirname $0`
MINUTES=0
DBDOWN=1
#FILECOUNT=0
#FILETRANSFERED=1


# Change to working directory.
cd $WORK_DIR

# Set environment variables.
export ORACLE_SID
export ORACLE_HOME
export PATH=$PATH:/oracle/product/9.2.0:/app/oracle/product/9.2.0/bin
export FILENAME

HERE IS THE EXECUTION PART

for filename in validation/cnt_*.sql
do
sqlplus -s > temp.log 2>&1 dbuser/passwd@$ORACLE_SID @$filename
if [ $? = 0 ]
then date +"%D %T: $filename successully counted." >> count.log
else date +"%D %T: Error counting $filename." >> count.log

The error message came from last line. Please advise the problems. Thanks.

Use absolute path for "validation/cnt_*.sql" like /x/y/z/validation/cnt_*.sql then give a try. by default cron will try to find that in /validation/cnt_*.sql which is causing the error..

gopidesaboyina:

Thanks so much. I do think you have pointed out the key point. It is not the env setting. It is the path. I have modified script to run manually success. I will come back to report the result that crontab run.

gopidesaboyina:

Sorry. I tried to use full path for the cnt_*.sql. It failed with the same message. Then, I manually run shell script. It run very fast and successfully. So what is the different between crontab execution environment and the environment that I logged in and manually execute the shell script? This is very weird and unbelieveable. Please advise. Thanks.

cron is running as root or other user id ? login with same id and try to run manually. let's c that works or not..can you paste the error again & if possible contents of sql u r running...