-
String Search And Data Load
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
-
guess some case sensitive problem ?
-
# 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.
-
Thanks For Your Guidance. I found from you for what I was looking for.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|