Oracle Database

Here's the script that i have generated…
Now, working on enhancement of this script….

#Checks whether the Inpu
t file exists. If file found the load process starts else throws error that file not found

if [ -f $file ]
then

if [ $(hostname) = elect ]
then
export userName="zx01182"
export ORACLE_SID=db86
export pwd_file=db86zx01182pw
elif [ $(hostname) = chi ]
then
export userName="zx01204"
export ORACLE_SID=db87
export pwd_file=db87zx01204pw
elif [ $(hostname) = zack ]
then
export userName="zx01205"
export ORACLE_SID=db88
export pwd_file=db88zx01205pw
else exit
fi

/usr/local/pl/util_scripts/perlencrypt.pl -k /export/home/oracle/secure/ealgorithm -d /sdr1/system/$pwd_file | sqlplus $userName @/space/dbexport/scripts/tr
uncate_phr_bene_1.sql

/usr/local/pl/util_scripts/perlencrypt.pl -k /export/home/oracle/secure/ealgorithm -d /sdr1/system/$pwd_file | sqlldr $userName control=$ControlFileNm log=
$logfile bad=/space/dbexport/PHR/bad/bene_eligibility-`date +%Y%m%d`.bad

rc=$?
if [ $rc == 0 ]
then
echo 'Bene_eligiblity table load Successful'
mv /space/dbexport/PHR/data/eligExport.csv /space/dbexport/PHR/archive/eligExport-‘date +%Y%m%d`.csv
chmod 640 /space/dbexport/PHR/archive/eligExport-`date +%Y%m%d`.csv
exit $rcelif [ $rc == 0 ]
then
echo ’Bene_eligiblity table load Successful'
mv /space/dbexport/PHR/data/eligExport.csv /space/dbexport/PHR/archive/eligExport-‘date +%Y%m%d`.csv
chmod 640 /space/dbexport/PHR/archive/eligExport-`date +%Y%m%d`.csv
/space/dbexport/PHR/log/bene_eligibility-`date +%Y%m%d`.log | to=moc.naidiron|55311di#moc.naidiron|55311di
basename=`basename $logfile`
subject="BENE ELIGIBILITY TABLE LOAD STATUS"
( echo ’BENE ELIGIBILTY LOAD PROCESS FINISHED SUCCESSFULLY'; uuencode $logfile $basename ) | mailx -s "$subje#ct" "$to"
status=`cat $variable`
status=`cat $logfile`
exit $rc

I'm not overly sure what you are trying to achieve here as I suspect most of the information you want will be in the sqlldr log which you could just email the users after the load has taken place.

cat logfile |mailx -s "Report to Users" email_address

However, if you want to check the file using the unix shell script then the best way to achieve this is as follows

1) The script should count the number of line in the Input.csv file and send an email to the users.

num_rows=`cat input.csv |wc -l`
echo $num_rows » someoutputfile.txt
cat someoutputfile.txt |mailx -s "Report to Users" email_address

2) The script should notify the users the the Input.csv file contains duplicate records in it (if any duplicate records exists).

dup_rows=`cat input.csv |sort |uniq |wc -l`
if [ $dup_rows -eq $num_rows ]
then "No Duplicates"
else cat "Duplicates" |mailx -s "Duplicate Rows in File" email_address
fi

3) If no duplicate records exists in the input.csv file and the data gets loaded into the Oracle table, the script should calculate the total no.

The above code has already established this. i.e. row_nums

Mike

Sorry, I noticed a couple of typos in the code above. (Teach me to write this off the top of my head) Point 2 code should be

dup_rows=`cat input.csv |sort |uniq |wc -l`
if [ $dup_rows -eq $num_rows ]
then echo "No Duplicates"
else echo "Duplicates" |mailx -s "Duplicate Rows in File" email_address
fi

Does this help?