SQL Loader with dynamic control file generation
Hi All,
Now what i am writing, I believe many of new bees will require it while using sqlldr for ETL or any other data loading issue. The thing is while loading using sqlldr there is no option to enter the source file name in that table. So what I have done I just create a dynamic control file which generates before loading of each source file. Here I have used two scripts
1) proc1: for filtering files and running a loop. Every source file will be processed in this loop.
2) another procedure proc2 which is called by proc1 to perform load operation
proc1
-------
while (( $i < $filecounter )); do
echo "file number $i"
### Take the first File
FILENAME=`ls -l *.txt|head -1|awk -F' ' '{print $9}'`
dos2unix $FILENAME
### if filename exists already continue the loop ###
FINDSQLSCRIPT="select 'COUNT:'||count(*) from TBLBRACSUMMARY where file_name='$FILENAME';"
OUTPUT_FILECOUNT=`/app/u01/oracle/product/10.2.0/db_1/bin/sqlplus -s /nolog < 0 ]]
then
echo "!!!!!!!!!!! duplicate found : $FILENAME !!!!!!!!!!!"
## continue with next file
i=$((i+1))
mv "$FILENAME" "$DUPDIR"
continue;
fi
cd "$DIRNAME"
### run the master (sqlldr) script ###
"$DIRNAME"/proc2.sh $FILENAME
i=$((i+1))
done
proc2
-------
CTL_FILE=test.ctl
bad=bad_`date +"%d-%b-%y"`
output=output_`date +"%d-%b-%y"`
user=userA
pass=userA
DATAFILEDIR=/oracle/scripts/dbatest/brac/datafiles/
BADFILEDIR=/oracle/scripts/dbatest/brac/badfiles/
OUTPUTFILEDIR=/oracle/scripts/dbatest/brac/outputfiles/
###### Create control file dynamically ######
cd /app/u01/oracle/scripts/dbatest/brac/
echo "----> SQLLOADER started with file $1 at "`date`
echo "LOAD DATA">"$CTL_FILE"
echo "APPEND">>"$CTL_FILE"
echo "INTO TABLE TBLTEST">>"$CTL_FILE"
echo "FIELDS TERMINATED BY ';'">>"$CTL_FILE"
echo "TRAILING NULLCOLS">>"$CTL_FILE"
echo "(">>"$CTL_FILE"
echo "SOURCE_FILE CONSTANT '$1',">>"$CTL_FILE"
echo "SLNO,">>"$CTL_FILE"
echo 'TXN_DATE timestamp "DD/MM/YYYY HH24:MI:SS",'>>"$CTL_FILE"
echo "BILL_ID,">>"$CTL_FILE"
echo "DEBIT,">>"$CTL_FILE"
echo "TOTAL_BALANCE,">>"$CTL_FILE"
echo 'FILESERIAL "SEQTEST.nextval"'>>"$CTL_FILE"
echo ")">>"$CTL_FILE"
sqlldr userid=$user/$pass control=/oracle/scripts/dbatest/test/"$CTL_FILE" data=$1 BAD=/oracle/scripts/dbatest/test/$bad.log log=/oracle/scripts/dbatest/test/$output.log
Now what i am writing, I believe many of new bees will require it while using sqlldr for ETL or any other data loading issue. The thing is while loading using sqlldr there is no option to enter the source file name in that table. So what I have done I just create a dynamic control file which generates before loading of each source file. Here I have used two scripts
1) proc1: for filtering files and running a loop. Every source file will be processed in this loop.
2) another procedure proc2 which is called by proc1 to perform load operation
proc1
-------
while (( $i < $filecounter )); do
echo "file number $i"
### Take the first File
FILENAME=`ls -l *.txt|head -1|awk -F' ' '{print $9}'`
dos2unix $FILENAME
### if filename exists already continue the loop ###
FINDSQLSCRIPT="select 'COUNT:'||count(*) from TBLBRACSUMMARY where file_name='$FILENAME';"
OUTPUT_FILECOUNT=`/app/u01/oracle/product/10.2.0/db_1/bin/sqlplus -s /nolog <
then
echo "!!!!!!!!!!! duplicate found : $FILENAME !!!!!!!!!!!"
## continue with next file
i=$((i+1))
mv "$FILENAME" "$DUPDIR"
continue;
fi
cd "$DIRNAME"
### run the master (sqlldr) script ###
"$DIRNAME"/proc2.sh $FILENAME
i=$((i+1))
done
proc2
-------
CTL_FILE=test.ctl
bad=bad_`date +"%d-%b-%y"`
output=output_`date +"%d-%b-%y"`
user=userA
pass=userA
DATAFILEDIR=/oracle/scripts/dbatest/brac/datafiles/
BADFILEDIR=/oracle/scripts/dbatest/brac/badfiles/
OUTPUTFILEDIR=/oracle/scripts/dbatest/brac/outputfiles/
###### Create control file dynamically ######
cd /app/u01/oracle/scripts/dbatest/brac/
echo "----> SQLLOADER started with file $1 at "`date`
echo "LOAD DATA">"$CTL_FILE"
echo "APPEND">>"$CTL_FILE"
echo "INTO TABLE TBLTEST">>"$CTL_FILE"
echo "FIELDS TERMINATED BY ';'">>"$CTL_FILE"
echo "TRAILING NULLCOLS">>"$CTL_FILE"
echo "(">>"$CTL_FILE"
echo "SOURCE_FILE CONSTANT '$1',">>"$CTL_FILE"
echo "SLNO,">>"$CTL_FILE"
echo 'TXN_DATE timestamp "DD/MM/YYYY HH24:MI:SS",'>>"$CTL_FILE"
echo "BILL_ID,">>"$CTL_FILE"
echo "DEBIT,">>"$CTL_FILE"
echo "TOTAL_BALANCE,">>"$CTL_FILE"
echo 'FILESERIAL "SEQTEST.nextval"'>>"$CTL_FILE"
echo ")">>"$CTL_FILE"
sqlldr userid=$user/$pass control=/oracle/scripts/dbatest/test/"$CTL_FILE" data=$1 BAD=/oracle/scripts/dbatest/test/$bad.log log=/oracle/scripts/dbatest/test/$output.log
Comments
Post a Comment