String Search And Data Load

[jayeshdba]

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

[Jr]

guess some case sensitive problem ?

[marek_wiechula]

# 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

In this code fragment you capture the first line of your file and you put the line into the variable header_record if it contains the character string ORACLE at any point. But nowhere else in your code do you look at ${header_record} to see if it actually contains anything.

Similarly with the last line in the file - you capture it if it contains the text ORACLE but you never test the variable afterwards to see if it has anything in it.

I am an awk enthusiast and I think it would be hard to test the first and last records directly in the shell. My Solaris system disagrees with my standard UNIX book on the awk index function. I have shown it as it works in Solaris. I would suggest...

header_test=`head -1 ${data_file}|awk '{t1=index($0,"ORACLE");if(t1>0){print "OK"} else {print "FAIL"}}'`
trailer_test=`tail -1 ${data_file}|awk '{t1=index($0,"ORACLE");if(t1>0){print "OK"} else {print "FAIL"}}'`
if [ "${header_test}" = "FAIL" -o "${trailer_test}" = "FAIL" ]
then
echo header or trailer line does not contain ORACLE.
exit
fi

If you want to make your comments more helpful to the user you can separate the tests for the header and trailer and report which failed.

I have another problem with the style of this script. It is not the source of your problem but it shows an uncertainty and vagueness about what you are doing.

# 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

As far as this code fragment is concerned what is the point of the DATA_RECVD? It is never referenced again and is unnecessary for this task. I would suggest:

1) reverse the condition and use it to exit directly
2) add a message to the user explaining the premature exit
3) test if the file is readable rather than whether it exists.

So, for example,

if [ ! -r ${data_file} ]
then
echo File ${data_file} does not exist or is unreadable.
exit
fi

There is no further need after this point to test if the data file is there or not (which you do THREE more times in your script). If the data file is not there (or in my case, is not readable) then you will have already exited the script. If you are still in the script you have a data file. No need to keep checking.

[jayeshdba]

Thanks For Your Guidance. I found from you for what I was looking for.