Friends,

I have prepared following unix shell scripts to search "ORACLE" word from header and trailor row, if string found in both header and trailor only then process should go ahead to load the data in the oracle table other wise it should exit.

I am facing problem in search of a string from header and trailor row.

#!/usr/bin/ksh

PATH=/usr/local/opt/oracle/product/ostl550/bin:/usr/bin:/usr/sbin:/usr/ucb:/usr/lbin:/usr/bin/X11:local/bin:/CC/bin:/local/cob31bin:/local/spf

ORACLE_HOME=/usr/local/opt/oracle/product/ostl550

ORACLE_SID=ostl550

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH

CUR_TIME=`date +%m%d%y_%R`

DATA_DIR=/appl/focsoc/jp3979/sotsofe/data
CTL_DIR=/appl/focsoc/jp3979/sotsofe/scripts
LOG_DIR=/appl/focsoc/jp3979/sotsofe/log
BAD_DIR=/appl/focsoc/jp3979/sotsofe/bad
DCD_DIR=/appl/focsoc/jp3979/sotsofe/dcd
ARCHIVE_DIR=/appl/focsoc/jp3979/sotsofe/archive_data

LOG_FILE=$LOG_DIR/$CUR_TIME.log
>$LOG_FILE
chmod 755 $LOG_FILE

bad_file=$BAD_DIR/$CUR_TIME.bad
discard_file=$DCD_DIR/$CUR_TIME.dcd
data_file=$DATA_DIR/sotsofe.txt

DB_USER=west271dly
DB_PASSWORD=west

# If data file is not received exit.

DATA_RECVD=0

if [ -f $data_file ]
then
DATA_RECVD=1
fi

if [ DATA_RECVD -eq 0 ]
then
exit
fi

# If the file is present continue loading into the table.


if [ -f $data_file ]

then

header_record=`head -1 $data_file | grep "ORACLE"`

mail -s "sotsofe.txt data file has header record " jp3979@sbc.com

else

exit

fi

if [ -f $data_file ]

then

trailor_record=`tail -1 $data_file | grep "ORACLE"`

mail -s "sotsofe.txt data file has trailer records" jp3979@sbc.com

else

exit

fi

done

if [ -f $data_file ]

then
echo "Loading " $data_file >> $LOG_FILE
sqlldr userid=$DB_USER/$DB_PASSWORD@$ORACLE_SID control=$CTL_DIR/sotsofe.ctl log=$LOG_FILE bad=$bad_file discard=$discard_file discardmax=2 errors=2
echo "Successfully Loaded " $data_file >> $LOG_FILE
mv $data_file $ARCHIVE_DIR/sotsofe.txt$CUR_TIME
echo "sotsofe.txt moved to archive directory " >> $LOG_FILE

fi

echo "Ending Process At " $CUR_TIME >> $LOG_FILE

mail -s "sotsofe.txt Data Load Job Finish" jp3979@sbc.com < $LOG_FILE



Pl Guide.
Jayesh