Shell Programming and Scripting

Hi All,
Am having a shell script JDE_REPORT_GEN.sh which intern calls a sql script JDE_CHANGED_DATA.sql. the problem here is when i run this script from the $prompt it is running fine and giving out the spool file required.

if i schedule the same shell in cronjob am not getting the spool file.Please advice this is very urgent and the content of the script is as below.
#################################
$cat JDE_REPORT_GEN.sh
#!/bin/sh

cd /tmp/azum

ORACLE_HOME=/asera/app/oracle/product/10.2.0/db_1
export ORACLE_HOME

sqlplus manta/manta@MTACS3 <<VITMAN @/tmp/azum/JDE_CHANGED_DATA.sql
exit
VITMAN
#######################################
$ cat JDE_CHANGED_DATA.sql
set echo off pagesize 49000 head off feed off veri off trimspool on
set linesize 30000
spool /tmp/azum/JDE_DATA_CHG_QTY_DUMMY_REPORT.csv

*****sql query here******

spool off;

##########################################


a prompt response to this is much appriciated.

thanks in agvance,
Azum.

Write the error into a file while scheduling in cron as urscript.sh 2>error.txt
And check what the error is. Most probably it will be the some env variable not set. Then you need to set the variable in ur script.

Thanks sudhamacs,
I wrotre the o/p of the shell to afile but the file got generated with no data in it. please help me further,

Regards,
azum.

cron only offers a limited environment. It doesn't mimic your profile. Meaning it probably doesn't know where the sqlplus command is. Doesn't know where JDE_REPORT_GEN.sh is, or I suppose it's possible you don't have the execute bit set on your script.

Along with sudhamacs suggestion, it would be a good idea to fully qualify any commands (this is a good security practice). So your cron command would be:

/bin/sh /full/path/to/script/JDE_REPORT_GEN.sh > /tmp/myoutput 2>&1

It would also be a good idea to create the /tmp/azum directory in your script.

Code:

if [ ! -d /tmp/azum ]
then
  mkdir -p /tmp/azum
  if [ $? -ne 0 ]
  then
    echo "ERROR: Cannot create spool directory"
    exit 1
  fi
fi

And maybe output a minimal amount of tracking to ensure your script is being called at all. Myabe:

echo "Starting at `date`"

And something else at the bottom. Also, end your script with:

exit 0