Re: "pass" return codes and/or variables

From: Jeff Rule <rule_at_apple.com>
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

Original text of this message