Oracle/UNIX Crontab

[tekpr00]

Hello:

I have a little script that:
1)logs into the database. it executes a statement.
2)if there are no rows, it send a message there are NO rows.
3)if there are rows in the file it send a message that there are rows.
Below is that sample of the script and the sql statement.
When I ran the script manually, it works properly.

HOWEVER, when I schedule it through CRONTAB, it does not work, it executes but give opposite message. That is, it says there are rows when there are no rows in the database.

Maybe there is a permission problem but all the files and directory have 775 permission. Please help

shell script

#!/bin/ksh
NAME=no
export ORACLE_SID=sid
export ORACLE_USER=sidname
export ORACLE_PW=$(crypt `hostname` < encrypted.pwd)

sqlplus -L ${ORACLE_USER}/${ORACLE_PW}@${ORACLE_SID} <<-SQLCMDS
@statement.sql
exit
SQLCMDS
!
chmod 775 statement.txt
if (grep  $NAME statement.txt); then
         echo "There are NO rows in READY STATE  on `hostname` "| mailx -s "Rows" 73319199@paging.com
   else
         echo "There are rows in READY STATE  on `hostname` "| mailx -s "Rows" 73319199@paging.com
   fi
mv statement.txt Log
cd Log
mv statement.txt statement-`date +%m.%d.%Y`
exit

SQL statement

spool statement.txt;
set heading off;

select enq_timestamp, msg_state, delay_timestamp
from   table

The spool file should contain - no rows selected
crontab entry

5 12 * * * . /etc/profile; . $HOME/.profile; /export/home/oracle/statement/statement.sh* > /dev/null 2>&1

 

[LKBrwnDBA]

You need to set complete/correct environment to the script because crontab will assign the sh shell instead of ksh shell and your .profile may not execute correctly.
Try adding those to your script as follows:

#!/bin/ksh
. $HOME/.profile
NAME=no
export ORACLE_SID=sid
ORAENV_ASK=NO
. /usr/local/bin/oraenv
export ORACLE_USER=sidname
export ORACLE_PW=$(crypt `hostname` < encrypted.pwd)
cd /export/home/oracle/statement
sqlplus ...etc ...

And you may need to remove the (!) after SQLCMDS.
And your crontab should look like this:

5 12 * * * /export/home/oracle/statement/statement.sh > /dev/null 2>&1

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb