Re: "pass" return codes and/or variables
Date: 1996/01/26
Message-ID: <310963AC.29D0_at_apple.com>
Thomas Wood wrote:
> > -- > > Is there away to pass return codes and/or variables from Oracle to Unix. I > have a batch job that will use SQL, export, and sqlloader. Each piece is
This example does not include export, but it does have sqlplus and sqlloader... It is a bit long, I have not edited the script down, but its purpose is to take a feed in the form of a text file and load it into an oracle database.
This does not do specific return values, but it does tell you if there was an error or not.
-Jeff
#!/bin/ksh
# ----------------------------------------------------------------------
# dept_update.com
#
# weekly department data update.
# update the Radar database with the department info from the ADB
# system exract.
# ----------------------------------------------------------------------
doForce=$1
. /ngs/app/expresso/RELEASE/scripts/expresso_setup.sh silent
inputfilename=apex_rollup.dat
inputfile=$EXPRESSO_BASE/data/$inputfilename
archivefile=$EXPRESSO_BASE/data/dept.data
echo "Starting dept_update.sh `date`...."
cd $EXPRESSO_BASE/data
# ----------------------------------------------------------------------
# if argument $1 is force then always reload
# ----------------------------------------------------------------------
if [ "$doForce" = "force" ]
then
rm -f $archivefile
fi
# ----------------------------------------------------------------------
# check if an hc_roll_text_file.rep file exists
# ----------------------------------------------------------------------
if [ \! -f $inputfile ]
then
# -- File not found echo "" echo "-------------------------------------------------------" echo "`date`" echo "The new DEPT extract has not arrived yet" echo "Ask for assistance. Page for support person" echo "-------------------------------------------------------" echo "" echo "" exit
fi
# File found
noNewDept=`find . -mtime 6 -name $inputFileName -print | wc -l`
if [ noNewDept -eq 1 ]
then
echo "" echo "-------------------------------------------------------" echo "`date`" echo "The new Department extract has not arrived yet" echo "Ask for assistance. Page for support person" echo "-------------------------------------------------------" echo "" echo "" exit
fi
if [ \! -f $archivefile ]
then
fileIsNew=1
elif [ `find . -newer $archivefile -name $inputfilename -print | wc -l` -eq 1 ]
then
fileIsNew=1
else
fileIsNew=0
fi
if [ $fileIsNew -eq 0 ]
then
# -- File is old echo "" echo "-----------------------------------------------" echo "`date`" echo "The new DEPT extract file is old " echo "Ask for assistance. Page for support person" echo "-----------------------------------------------" echo "" echo "" exit
fi
# -- File is new
cp -f $inputfile $archivefile
if [ $? -ne 0 ]
then
echo "" echo "---------------------------------------------" echo "`date`" echo "Failure copying $inputfile to $archivefile" echo "Ask for assistance. Page for support person" echo "---------------------------------------------" echo "" echo "" exit
fi
chmod 660 $archivefile
if [ $? -ne 0 ]
then
echo "" echo "---------------------------------------------" echo "`date`" echo "Failure chmod 660 $archivefile" echo "Ask for assistance. Page for support person" echo "---------------------------------------------" echo "" echo "" exit
fi
echo ""
sqlplus -s /nolog <<EOF
whenever sqlerror exit failure whenever oserror exit failure connect expresso_batch/xxx host echo "Calling ExpressoDDL.TruncateTableDeptInfo database procedure: `date` ...." execute ExpressoDDL.TruncateTableDeptInfo;EOF if [ $? -ne 0 ]
then
echo "" echo "---------------------------------------------" echo "`date`" echo "Failure while in ORACLE." echo "Ask for assistance. Page for support person" echo "---------------------------------------------" echo "" echo "" exit
fi
rm -f $EXPRESSO_HOME/log/dept.*
if [ $? -ne 0 ]
then
echo "" echo "---------------------------------------------" echo "`date`" echo "Failure in rm -f $EXPRESSO_HOME/log/dept.*" echo "Ask for assistance. Page for support person" echo "---------------------------------------------" echo "" echo "" exit
fi
echo ""
echo "Calling SQL Loader to load data into oracle: `date`...."
sqlldr userid=xxx/xxx direct=true control=$EXPRESSO_HOME/scripts/dept.ctl log=$EXPRESSO_HOME/log/dept.log
silent=\(HEADER,FEEDBACK\)
if [ $? -ne 0 ]
then
echo "" echo "---------------------------------------------" echo "`date`" echo "Failure in sqlldr loading department data file" echo "Ask for assistance. Page for support person" echo "---------------------------------------------" echo "" echo "" exit
fi
chmod 777 $EXPRESSO_HOME/log/dept.*
if [ $? -ne 0 ]
then
echo "" echo "---------------------------------------------" echo "`date`" echo "Failure in chmod 777 $EXPRESSO_HOME/log/dept.*" echo "Ask for assistance. Page for support person" echo "---------------------------------------------" echo "" echo "" exit
fi
echo ""
sqlplus -s /nolog <<EOF
whenever sqlerror exit failure whenever oserror exit failure connect expresso_batch/xxx host echo "Calling DeptDataUpdate database procedure: `date` ...." execute deptdataupdate;
EOF
if [ $? -ne 0 ]
then
echo "" echo "---------------------------------------------" echo "`date`" echo "Failure while in ORACLE. " echo "Ask for assistance. Page for support person" echo "---------------------------------------------" echo "" echo "" exit
fi
#purge/keep=3 dept.data
echo "" echo "-----------------------------------------------" echo "`date`" echo "The Department load into Radar has Completed" echo "Processing Successful." echo "-----------------------------------------------" echo "" echo ""
exit
# dcm_message "Radar Department Update was Not Successful - No new extract"
# dcm_message "An error has occured with the Radar Department Update"
Received on Fri Jan 26 1996 - 00:00:00 CET