CRON and SQLPLUS

Question

I want to run a script, which contains some sqlplus commands, in cron.

The problem is, that the sqlplus command won't be executed for some reason, when executed in cron. If I execute it by myself, the script runs fine.

I've checked some forums, even the topics here on stackoverflow.com and found some tips regarding the correct setting of environment variables. But even after double checking this, the script doesn't work.

Here is my script:

echo $ORACLE_HOME|grep "oracle" > /dev/null
if [ $? = 1 ] ; then
   echo "Setting environment variable"
   # Setting oracle environmet
   . /usr/oracle/product/10.2.0/.profile
   NLS_LANG='AMERICAN_GERMANY.WE8ISO8859P1'
fi

/usr/oracle/product/10.2.0/bin/sqlplus username/password @basics.sql > export.file

basics.sql contains:

set pagesize 0
set feedback off
set heading off
set linesize 400
set NULL nll

SELECT SOME_FIELDS FROM TABLE ORDER BY FIELD;
EXIT;

Any ideas?

So the shell script is executing but not SQL? How are you executing SQL? Can you elaborate? — Kevin Rave
sql is executed in /usr/oracle/product/10.2.0/bin/sqlplus username/password @basics.sql > export.file — Marco Frost
When you say, its not executing, are you getting any errors? — Kevin Rave
What do you think the current directory is when your script is run by cron? — mustaccio
Hm. The directory is /root. Maybe I should specify a absolute path for basics.sql... — Marco Frost

ik_zelf ik_zelf · Accepted Answer · 2013-07-29T12:27:28

shell environment is very important for Oracle and almost not there when using cron. As always there are several ways to solve this.

use full qualified paths - a bit inflexible
make the script to setup it's own execution environment
setup the execution environment in cron, when calling the script.

A pretty much standard way of setting up your environment from withing the script is by using the oraenv script, normally located in /usr/local/bin

ORACLE_SID={your_sid}
ORAENV_ASK=NO
type oraenv >/dev/null 2>&1 || PATH=/usr/local/bin:$PATH
. oraenv
SQLPATH=$HOME/sql
export SQLPATH
do your stuff

from the cron line:

10 10 * * * $HOME/.profile;$HOME/bin/your_script >$HOME/log/your_script.log 2>&1

This assumes that the .profile is not interactive and export the needed environment.

CRON and SQLPLUS

1 Answers