crontab and shell script that executes a sql.

Shell Programming and Scripting
1

Problem:
I have a crontab and when it kicks off, xxx.sh shell script is called. Which has a nohup sqlplus session call. Problem is sql does not get executed and a text file is not getting created. Only a empty log file is getting created. Are there any constraints for crontab to open a sql plus session? Why is the sql not spooling the data? If I execute this shell script on command line every thing works nicely.

I have a crontab that is calling a shell script:
28 14 * * 1-5 $HOME/xxx.sh >> $HOME/xxx.log

code in xxx.sh:
nohup sqlplus userid/password@host @${SCRIPTSD}/xxx.sql ${OUTPUTD}/outfile.txt $datamonth > ${SCRIPTSD}/xxx.log &

wait
exit 0

Only the xxx.log file is getting created. No data file is getting created!
Does anybody know what's the problem.

ThankYou,
Radhika.

2

Radhika, please read the FAQs and search before posting.

This is most likely because the environment is not set up to run the commands in your script from cron.

the user in this thread had the same problem. This tread was a direct link from the cron and crontab faq.

3

2 suggestions:

  1. redirect stderr to a file, to see what happens.
    28 14 * * 1-5 $HOME/xxx.sh >> $HOME/xxx.log 2>>error.log

  2. add relative environments for sqlplus in xxx.sh.

4

I am sourcing the file by putting the following command in the shell script:

. .profile

It still doesn't work. I am going to try the trace on and see what happens.

Thanks,
Radhika.

5

What shell is the the users login shell? It it is a csh or tcsh user then .profile will not be the one used to set up the user environment.

6

It is kshell.

7

It worked! I was sourcing the wrong profile file- the one with application variables and not the generic one with environment set up information. I guess it IS! time to call it a day.

ThankYou! all for your suggestions.
Radhika.