##########################################################
#
# Purpose:  Reads dp_inv_package.lst and submits lines
#           in sequence to SQL*Plus or SQL*Loader in order
#           to build DP_INV schema.
#
# History:
#
# when           who     what
# ----           ---     ----
# 01-Mar-2004    JJM     started.
#
##########################################################
STARS="**************************************************"
TIMESTAMP=`date "+%m/%d/%Y %H:%M:%S"`
LOGFILE=dp_inv_install.log.`date "+%m%d%Y.%H:%M:%S"`
PACKLIST=./dp_inv_package.lst
DBUSER=system
DBPWD=nitram
#DBNAME=Lucky 
##########################################################
checkSQLStatus ()
{
if [ $# -lt 2 ] ; then
       echo "Usage: $0 <caller> <logfile>"
       return -1
fi

CALLER=$1
myLOG=$2

if [ ! -a $myLOG ] ; then
    echo "$0: Can't access $myLOG"
    return -1
fi

ORACLE_ERRS=`grep -e ORA- -e SP2- $myLOG | awk '{print $1}'`

#list of non-fatal ORA errors.
for token in $ORACLE_ERRS
do
  enableTRAP=1
  # table or view does not exist
  if [ $token = "ORA-00942:" ] ; then
     enableTRAP=0
  fi

  # name is already used by an existing object
  if [ $token = "ORA-00955:" ] ; then
     enableTRAP=0
  fi

  # column being added already exists in table
  if [ $token = "ORA-01430:" ] ; then
     enableTRAP=0
  fi

  # preceding line from SAP 
  if [ $token = "ORA-02063:" ] ; then
     enableTRAP=0
  fi

  if [ $enableTRAP = 1 ] ; then
       # make the calling script exit with SQLCODE
       RC=`echo $token | exec cut -c5-9`
       echo "\n$0 aborting on ORACLE ERROR: $token\nSee $LOGFILE for details\n" 
       exit $RC
       # yes, there's a break after exit
       break
  fi
done

# return success to the caller, don't exit it
return 0
}
##########################################################
echo "\n$STARS\n$TIMESTAMP:	$0 Starting" > $LOGFILE 

# parse the cofiguration file
if [ ! -a $PACKLIST ] ; then
    echo "$0: Can't access $PACKLIST" | tee -a $LOGFILE 
    exit -1
fi

export IFS="|"
while read -r COMMAND SCRIPT COMMENT
do

    # ignore comments and blank lines
    COLONE=`echo $COMMAND | exec /usr/bin/cut -c1-1`
    if [ -z "$COLONE" ] ; then
        continue
    fi
    if [ "$COLONE" = '#' ] ; then
        continue
    fi

    echo "\nRunning: $COMMAND $SCRIPT\n" | tee $LOGFILE 

    # run script
    if [ "$COMMAND" = 'sqlplus' ] ; then
        sqlplus $DBUSER/$DBPWD@$DBNAME @$SCRIPT >> $LOGFILE
        checkSQLStatus "$0" $LOGFILE 
    fi
    if [ "$COMMAND" = 'sqlldr' ] ; then
        sqlldr userid=$DBUSER/$DBPWD control=$SCRIPT >> $LOGFILE
        checkSQLStatus "$0" $LOGFILE
    fi

done < $PACKLIST
echo "\n$TIMESTAMP:	$0:\nNormal Successful Complettion\n$STARS\n" >> $LOGFILE

###########################################################
#exit with success code
exit 0