Unix Crontab

Hi
i have written a shell script which runs some sql queries in oracle.
the code looks like this

sqlplus username@NJCMTOMS/password < $HOME/devel/oms/arg/cleanupsql.sh

cleanupsql.sh has the sqls to be executed.

This works fine if i run from the prompt but when i run from a cron,then cron is not able to find sqlplus.i also tried giving the absolute path for sqlplus ,still no success.

Pls tell me what is the exact way of doing this.

Hi

I've also got the same issue. I've created a ksh that uses sql+ to do select statements. It also works fine when executed from the prompt, but does not work when scheduled to run on cron.

The sql in my script is like this:

sqlplus -s USERNAME/PASSWORD@DATABASE <<END >$wsfile
set heading off
SELECT SQL QUERY HERE
/
END

I've tried adding the oracle home to the script but this still does not help.

Any assistance will be greatly appreciated.

I found the solution.
I had to include:

export ORACLE_HOME=/ora/product/v9203

Also to define the full path for the sql path.

/ora/product/v9203/bin/sqlplus -s user/password@database

Whenever creating scripts run by cron, always

- include full paths to all necessary executables
i.e. /usr/bin/cat and so on.

- define all necessary environmental variables.
and export same.

Do not assume that cron runs the script in your
environment. It does not.

- Finnbarr