Running an sql script from crontab

[maxcrook]

I have an sql script in my home directory that I need to run from crontab. The script works when executed from the command line however when placed in crontab the script fails and the mail in /usr/spool tells me that sqlplus cannot be found. The script is as follows: Anyone know what I am doing wrong ?

sqlplus <<!
<username>/<password>
@maxtestsql.sql
exit
!

 

[crowe]

Post the crontab entry please. It sounds like an absolute path issue.

crowe

 

[maxcrook]

The script mentioned above is called test_sql and the crontab entry is

41 13 02 * * /home/mcrook/test_sql

 

[crowe]

When you run the script from your home directory, what user are you logged in as? The posted crontab, is it root's crontab, or another users crontab? I suspect that if you are running the command from your home directory /home/mcrook as user mcrook, the user mcrook has an entry in the $PATH variable pointing to the sqlplus command. However, possibly root does not have the same $PATH variable entry to this command.
Locate where the sqlplus command lives (which sqlplus). The check root's $PATH to see if the path is there (logged in as root, echo $PATH).

crowe

 

[MikeLacey]

maxcrook,

could i suggest you call a script from cron rather than calling sqplus directly?

your script should look something like this:
[tt]
ORACLE_HOME=/mnt/sw/oracle/product/8.1.6
ORACLE_SID=MYDB
PATH=$PATH:$ORACLE_HOME/bin
sqlplus ....
[/tt]
you should, of course, fix up the values for ORACLE_HOME and ORACLE_SID so that they match your environment -- if you're not sure what they should be, login as oracle or whoever you would normally run sqlplus as and:

echo $ORACLE_HOME
echo $ORACLE_SID

Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[maxcrook]

Hi everyone thanks for your help - I now have the following in my script and everything works fine.

export ORACLE_HOME=/u01/app/oracle/product/7.3.4.4
export ORACLE_SID=genp
export PATH=$PATH:$ORACLE_HOME/bin
sqlplus <<!
geneva_admin/gaprodgenp
@maxtestsql
exit
!

Much appreciated. Thank you all again.