Hi,

Everyday I get 1 or more flat files, it is sure that I get one file but it can be more, I want to sql load all the files into a table. I know how to do this for one file but for multiple files I should be able to do that in a loop. Could you please tell me how to do this in shell script? ie. How to check how many files are there in particular and access them?

Thanks in advance.

Sudha

Try this:

#!/bin/ksh

dir='/home/myfiledir'
bkpdir='/home/mybkpdir'
LOAD_LOG=/tmp/mylog.log

echo "There are $(ls -1 $dir|wc -l) files in $dir"

for fil in $(ls -1 $dir)
do
echo "Loading $fil ..."
sqlldr / control=myfile.ctl DATA=$fil LOG=$LOAD_LOG
if [[ "$(grep ^ORA- $LOAD_LOG | wc -l)" -gt 0 ]]
then
echo "Oracle error(s) occured, check log $LOAD_LOG"
exit 3
fi
REC_CNT=$(grep ' Rows successfully loaded.' $LOAD_LOG|awk '{print $1;}')
BAD_REC=$(grep 'Total logical records rejected:' $LOAD_LOG|awk -F: '{print $2;}')
DSC_REC=$(grep 'Total logical records discarded:' $LOAD_LOG|awk -F: '{print $2;}')
REC_CNT=${REC_CNT:=0}
if test $REC_CNT -eq 0
then
echo "!Error: SQL LOADER FAILED, No records loaded."
exit 3
fi
ERR_CNT=$(expr $BAD_REC + $DSC_REC )
if [ ${ERR_CNT:-1} -ne 0 ]
then
echo "!Error: SQL LOADER FAILED, Bad or Discarded records."
exit 3
fi
echo "$REC_CNT Rows successfully loaded."

mv $fil $mybkupdir

done

Thanks LKBrwn_DBA,


I want to count number of files of particular pattern say x*.txt. Could you please tell me how I do that? ls -1 $dir|wc gives no of files in the dir, right?

Sorry I should have been clear.

Thanks in advance,

Sudha

Hi,

I sorted that ls -1 $test*.txt |wc gave me what I wanted.

Sudha

Hi LRBrwn_DBA,

i am getting these errors

There are $(ls -1 /dist/scripts/testdir/|wc -l) files in /dist/scripts/testdir/
instead of
There are 20 files in /dist/scripts/testdir/
and
tt.ksh: syntax error at line 4: `$' unexpected where
line 4 is
for fil in $(ls -1 $dir)

could you please tell me why this is happening

Thanks in Advance

Sudha

Maybe $(...) doesn't work inside quotes?

Try this:

echo "There are `ls -1 $dir|wc -l` files in $dir"

Where '`' is the back quote.

Hi LKBrwn_DBA,

Thanks, i will try that...

Sudha