CODEWITHSUNDEEP
oracle-databaseshellcronsqlplus
sugunan
sugunan

Reputation: 4466

sqlplus connection error on crontab but works directly

I'm using following script to create text file with oracle query output. The script work fine when I try it manually. But when I place it to crontab it is permanently fail to get the connection. Is there any restriction to access sqlplus through automation?

rm /export/home/oracle/out.csv 
INC=0


while : ; do    

INC=$(($INC+1))
if [[ "$INC" -eq 10 ]]; then
    echo "Exit after multiple failed attempt to connect to the DB."
    break
fi

sqlplus -s username/[email protected]:1552/servicename << EOF
set pagesize 10000

set feedback off
set heading off
set echo off

spool /export/home/oracle/out.csv 
SET LINESIZE 10000
SET PAGESIZE 50
SELECT TRIM(COUNT(*)) FROM users;
SPOOL OFF
EXIT;
EOF

[[ -f "/export/home/oracle/out.csv" ]] && break
echo "Failed to connect to DB and retrying."
sleep 5

done

Upvotes: 1

Views: 2512

Answers (1)

Daniel Vukasovich
Daniel Vukasovich

Reputation: 1742

Cron utility uses "/bin/sh" as default shell, so you need to setup Oracle environment accordingly.

You may:

1.- Have a configuration file containing the necessary variables. (*)

2.- Hard code the necessary Oracle environment variables in your script.

In your case:

For solution "1", you can create $HOME/profile.12cR2 file with the following content: 

export ORACLE_BASE=/oraclebin/app/oracle/product/databaseR2/base
export ORACLE_HOME=/oraclebin/app/oracle/product/databaseR2/12cR2

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export ORACLE_SID=db02

export EDITOR=vi

And update your script as follows:

. $HOME/profile.12cR2

rm /export/home/oracle/out.csv 
INC=0


while : ; do    

INC=$(($INC+1))
if [[ "$INC" -eq 10 ]]; then
    echo "Exit after multiple failed attempt to connect to the DB."
    break
fi
...

(*) Update this configuration file according your Oracle database infrastructure.

Upvotes: 2

Related Questions