Need for troubleshooting in UNIX w/ shell script program and IF..THEN command.

Question

Since you guys are having lots of questions or not enough info to work with, below is proprietary information and as such be careful as you can. The question – (please refer to if [$7 -eq "AAA"]...then statement - in bold ) has a problem with an output, where I can only output/print count of 'AAA' regardless the variable coming in either 'BBB' or ‘AAA’. As a result, and as an error, the program outputs/prints count of ‘AAA’.

Again, in this situation I tried with my best knowledge to explain to those available and savvy. Thanks.

-----------------------------------------------------------------------
# Print the run date
echo "**********************"
echo " TALK Program "
echo " Run Date " 
     date
echo "**********************"

#            DIRECTORIES/VARIABLES/PARAMETERS

p1=$1
p2=$2
p3=$3
p4=$4
p5=$5
p6=$6
p7=$7
p8=$8

echo "***********************************************"
echo "The parameteres are"
echo "                                  "
echo "  Param 1 (Username/Password)   = "
echo "  Param 2 (User account number) = "$2
echo "  Param 3 (Requestor name)      = "$3
echo "  Param 4 (Requestor ID)        = "$4
echo "  Param 7 (AAA or BBB)          = "$7
echo "  Param 8 (TALK Data Filename)  = "$8
echo "***********************************************"

AREA1=$WILLS_TOP/bin                      # Switch-variable points to GENEX bin dir
AREA2=$WILLS_TOP/sql                      # Switch-variable points to GENEX sql dir

SQL_CTL=$AREA1/WILLSTALK.ctl              # Name of SQL*Load ctrl file                  
SQL_VALID=$AREA2/WILLSTALK.sql            # Name of validation PL/SQL script

FTP_BIN=/usr/local/bin                    # Where ftp program is
UNIX_GET=$FTP_BIN/ftpmget.sh              # Name of UNIX file get program
UNIX_PUT=$FTP_BIN/ftpmput.sh              # Name of UNIX file put program
UNIX_DEL=$FTP_BIN/ftpdel.sh               # Name of UNIX file delete program 

TEMP=/tmp                                 # Name of temp dir

INPUT_DIR=/oracle/inbound                 # For DVLP  Where input file is comming from
ARCHIVE_NT_DIR=/oracle/inbound/RFS_DVLP   # For DVLP  Modified on 2005

OUTPUT_DIR=/oracle/outbound/              # Where output file is going to
OUTPUT_UNIX_DIR=$OUTCSF/$$OUTLOG/         # Directory of where output is
DES_NAME=${OUTPUT_UNIX_DIR}${OUT_FILE}    # Where output is going to be

USERACCNUM=$p2                            # User Account Number
USERNAME=$p5                              # Username
PASSWORD=$p6                              # Password
AAA_OR_BBB=$p7                            # Either 'AAA' or default of 'BBB'
DATAFILE_NAME=$p8                         # Datafile name (NT)  
SERVER='172.........'                     # Server (NT)
LOG_DIR=$WILLS_TIP/log                    # Path of log dir
LOCAL_DIR=$WILLS_TIP/data                 # Directory where file is at (UNIX)
ARCHIVE_DIR=$WILLS_TIP/archive            # Directory for archive (UNIX)
FILE_NAME=""                              # File name (UNIX)
FILE_TRAN_TYPE='ascii'                    # File transfer type

#*****************************************************************************
#                               MAIN PROCEDURE
#*****************************************************************************
if [ ! "$p7" ]
then
     echo "At least one MS Excel Update Data File must be entered !"
     echo "No files were entered."
     exit 1
fi

#----------------------------------------------------------------------
#  Remove all MS Excel datafile in $GENEX_TOP/data directory if exist
#----------------------------------------------------------------------
rm -f $WILLS_TIP/data/TALK* 

#--------
#  FTP
#--------
${UNIX_GET} $p5 $p6 $SERVER $INPUT_DIR $DATAFILE_NAME $LOCAL_DIR "" $TEMP $FILE_TRAN_TYPE 

if [ $? != 0 ]
then
    echo "The MS Excel Datafile(s) ftp has failed."
    exit 1
fi

if [ ! -s $LOCAL_DIR/$p8 ]
then
    echo "FTP command executed, but MS Excel file(s) did not get copied."
    exit 2
fi

#-----------------------------------------
# SQL*Loads - New MS Excel file to load
#-----------------------------------------

for i in $LOCAL_DIR/$DATAFILE_NAME   
do
   SQLOAD_DAT_FILE=`echo $i | sed "s/\// /g" | awk ' { print $NF } '`

   SQLOAD_LOG_FILE=$SQLOAD_DAT_FILE.log         # Sqlload log file name
   SQLOAD_BAD_FILE=$SQLOAD_DAT_FILE.bad         # Sqlload bad file name

   if [ -s $LOG_DIR/$SQLOAD_BAD_FILE ]
   then
       rm -f $LOG_DIR/$SQLOAD_BAD_FILE  # Remove prev. bad file, if such exists
       echo "Previous bad file has been successfully removed."
   else
       echo "No bad file was identified and removed."
       echo "Resume Normal Processing."
   fi

   echo $i
   echo "Now loading datafile:  $LOCAL_DIR/$SQLOAD_DAT_FILE"

   #---------------------------------------------------------------
   sed "s/@USERACCNUM/$USERACCNUM/" $SQL_CTL > $TEMP/WILLSTAL.ctl  # Changing '@USERACCNUM' 
   #--------------------------------------------------------------- 

   sqlldr userid=${p1} control=$TEMP/WILLSTALK.ctl \
             log=$LOG_DIR/$SQLOAD_LOG_FILE \
             bad=$LOG_DIR/$SQLOAD_BAD_FILE \
            data=$LOCAL_DIR/$SQLOAD_DAT_FILE

   if test ! -s $LOG_DIR/$SQLOAD_BAD_FILE
   then
        tail -20 $LOG_DIR/$SQLOAD_LOG_FILE
        echo "*** Done SQL*Loader Script ***"
   else
        echo "The SQL*Load for MS Excel Update file has FAILED due to BAD DATA."
        echo "*** Exitting Script On Failure ***"
        echo "*** Process is ready for NEXT LOAD! ***" 
        exit 1
   fi
done

#----------------------------------------
#       To run a PL/SQL script
#----------------------------------------
sqlplus -s $p1 @$SQL_VALID $AAA_OR_BBB
if [ $? -ne 0 ]
then
    echo "The PL/SQL script for validation has FAILED."
    exit 1
fi

#---------------------------------------------------------- 
# Now run SQL*Plus script Flat File Report After Updating 
#----------------------------------------------------------

sqlplus -s $1 <<EOF

set echo off
set heading off
set space 0
set newpage 0
set pagesize 0
set feed off
set term on

SPOOL $OUTCSF/$OUTLOG/l$4.log

SELECT 'spool $OUTCSF/$APPLOUT/o$4.out' FROM dual;

SPOOL OFF

@$OUTCSF/$OUTLOG/l$4.log

Select 'Run Date: '||to_char(sysdate, 'DD-MON-YYYY') From dual;

Select '*************************************************************' From dual; 

SELECT 'ERRORS Found: '||count(*) 
  FROM gnx_rel_date_temp   
 WHERE error_text IS NOT NULL;

Select '--------------- ----------- ---------------------------------' From dual;
Select 'TRX NUMBER      NEW DATE                           ERROR TEXT' From dual;
Select '--------------- ----------- ---------------------------------' From dual;
SELECT trx_num||'|'||new_date||'|'||error_text
  FROM will_rel_date_temp   
 WHERE error_text IS NOT NULL;

Select '*************************************************************' From dual;

#-------------
#---- AAA ----
#-------------
if [ $7 -eq "AAA" ] then
   SELECT 'Total AAA Updated: '||count(*)||' records'         
     FROM reldatetemp             
         ,allcustomers     
    WHERE trx_num = trx_no       
      AND error_text IS NULL       
      AND attr2 IS NOT NULL          
      AND attr2 = TO_CHAR(new_date,'YYYY/MM/DD HH24:MI:SS');  
else
    exit 0
fi
#---------------
#---- BBB ----
#---------------
if [ $7 -eq "BBB" ] then
   SELECT 'Total BBB Updated: '||count(*)||' records'         
     FROM reldatetemp             
         ,allcustomers     
    WHERE trx_num = trx_no       
      AND error_text IS NULL       
      AND attr1 IS NOT NULL          
      AND attr1 = TO_CHAR(new_date,'YYYY/MM/DD HH24:MI:SS');  
else 
    exit 0
fi

SPOOL OFF
EOF

#----------------------------------------------
#   To delete the data files on NT
#----------------------------------------------
${UNIX_DEL} $p5 $p6 $SERVER $INPUT_DIR $DATAFILE_NAME $TEMP

mv $WILLS_TIP/data/$DATAFILE_NAME $WILLS_TIP/archive/. 

#  All Done!
echo "*** End of program *** "

Answer 1

Anything between "sqlplus -s $1 << EOF" and "EOF" is called "here-doc" and intended for consumption by sqlplus. You can't really expect sqlplus to understand shell scripting in the form of "if [ "$7" -eq ....] ".

You should make all "ifs" outside of the here-doc and use results in it. Like this:

if [ "$7" -eq "AAA" ] ; then
  attr="attr2"
elsif [ "$7" -eq "BBB" ] ; then
  attr="attr1"
else
  echo "Bogus $7 - need AAA or BBB"
fi

....

sqlplus -s $1 <<EOF
...
SELECT 'Total $7 Updated: '||count(*)||' records'         
 FROM reldatetemp             
     ,allcustomers     
WHERE trx_num = trx_no       
  AND error_text IS NULL       
  AND ${attr} IS NOT NULL          
  AND ${attr} = TO_CHAR(new_date,'YYYY/MM/DD HH24:MI:SS');
...
EOF

Pay attention to "${attr}" and read a decent tutorial on shell scripting.

Answer 2

Hi

How can the script possibly continue after this code:

#-------------
#---- AAA ----
#-------------
if [ $7 -eq "AAA" ] then
   SELECT 'Total AAA Updated: '||count(*)||' records'         
     FROM reldatetemp             
         ,allcustomers     
    WHERE trx_num = trx_no       
      AND error_text IS NULL       
      AND attr2 IS NOT NULL          
      AND attr2 = TO_CHAR(new_date,'YYYY/MM/DD HH24:MI:SS');  
else
    exit 0
fi

If $7 = "AAA" then run the select, if $7 is not = "AAA" exit the script.....

Regards
K

Answer 3

ADEpt -

I have followed your advice and ran SQL query using Shell script. Well, I'm not an expert in here-doc, but it seems like that a few syntax or variables, components are still missing in my SQL Select…From program. This param - ${attr} that SQL select didn't like at all, as well as the tables, etc. Why?

Here are the results:

Run Date: 18-NOV-2008

---

ERRORS Found: 0

---

TRX NUMBER NEW DATE ERROR TEXT

---

---

unknown command beginning "FROM gnx_r..." - rest of line ignored. unknown command beginning ",ra_custom..." - rest of line ignored. unknown command beginning "WHERE trx_..." - rest of line ignored. unknown command beginning "AND error_..." - rest of line ignored. For a list of known commands enter HELP and to leave enter EXIT. unknown command beginning "AND IS NO..." - rest of line ignored. unknown command beginning "AND = TO_..." - rest of line ignored.

Additional help is much appreciated. Thanks