I have created a Shell script to call 12 Oracle PL/SQL scripts to query some data and insert into a table. When I run shell scripts manually, it works very well. But when I schedule shell script in crontab, it will give the error message like this:

02/22/07 04:00:00: Database mydb.world is up and running.

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

02/22/07 04:00:00: Error counting validation/PL_SQL_1.sql.
02/22/07 04:00:00: Exiting script.

It seems that crontab started to execute shell script, when it went to call PL/SQL scripts, it failed. So I think that I may missed something in crontab settings. But I don't know what it is after I man crontab. I also post a part of my shell script for your information. Please help me to solve this problem. Many thanks in advance.

Here is the code:

# Started to insert records into xxxx_rowcount table.
for filename in validation/cnt_xxxx_*.sql
do
sqlplus -s > temp.log 2>&1 db_stg/db_passwd@$ORACLE_SID @$filename
if [ $? = 0 ]
then date +"%D %T: $filename successully counted." >> row_count.log
else date +"%D %T: Error counting $filename." >> row_count.log
grep "ORA-" temp.log >> row_count.log
date +"%D %T: Exiting script." >> row_count.log
mail -s 'CAFMII Load: Unsuccessful. See log file for errors.' btmna@yahoo.com<row_count.log
exit 1
fi
done

Highlighted part is in error message. It seems that script couldn't count $filename. Do I need to add filecount=xx in the script. Please advise.

why don't you try to execute your .profile before running the script.

like this:

Thanks. Why do I need to execute .profile before executing this shell script?
Please give me more tips. Is this for the environment variables settings?

Yes, it is for setting the environment. You mentioned that you were able to run the script successfully manually with your login and the same script was failing from the crontab. so the most obvious reason could be the environment settings present in your .profile file( it is executed every time you login so the environment settings are set as soon as you login). whenever the script is invoked from cron it does not execute the .profile file so the environment settings are missing and leading to the script failure.

Your explanation may be correct. But I have other 3 shell scripts to be executed under the same user. Two of them do the same job to call many PL/SQL scripts to do the work. One of them was created recently and is to call two PL/SQL to do the work. I scheduled 3 shell scripts in crontab without running .profile. The same environment and Solaris shell on these shell scripts. This is why I am confused. I think it may be the coding in shell scripts that can not go through the list of PL/SQL files. The error message is not clear for me to identify the reason on this. But I will try your method to run .profile to make envirnment ready. Then I will report the result. Thanks.