Oracle SQL Loader - Insert filename into table

 How to insert the name of your data file into the load table

I was tasked with a problem today on a project where one of the requirements was to load data from a flat file into a DB table, but at the same time also load the name of the file. The file name is unique and contains information about country of origin, sequence number and date.
My first thought was does SQL Loader accept parameters! It doesn't. After a little web searching I came across this eloquent solution for this very problem.

To allow SQL Loader to insert the input file name into output table you first have to update your control file (myControlFile.ctl) with a placeholder for the file name (:FILE).


Load data
truncate
into table MY_TABLE
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
UNIQUEREF,
ID,
QTY,
ETA DATE"DD/MM/YYYY",
FILENAME CONSTANT ":FILE"
)


Then update shell script with the following.  This will Loop through files in current directory and load each one. The cat / sed command will create a new control file and replace the placeholder (:FILE) with the name of your csv file.


FILES=`ls *.csv`
CTL=myControlFile.ctl
for f in $FILES
do
   cat $CTL| sed "s/:FILE/$f/g" > $f.ctl
   sqlldr usr/pass control=$f.ctl data=$f bad=mybad.bad discard=mydsc.dsc log=mylog.log ERRORS=1000000
done 

The file name should now be successfully loaded into the MY_TABLE.