Re: SQLLDR - How to set value based on the infile?

peng_cynthia_at_hotmail.com (Cynthia Peng) wrote

> I am loading data into a table from multiple files. How do you set
> value to a field based on the particular source file. Didn't find the
> answer at the first look at the 9i Database Util doc. Any
> insight/comment is greatly appreciated.
>
> ===================example=======================================
> load data
> infile "F:\a\formats.ing" "str"
> infile "F:\b\formats.ing" "str"
> infile "F:\c\formats.ing" "str"
> infile "F:\d\formats.ing" "str"
>
> append
>
> into table tmp_formats
> (notif_fmt position(6:8) , name CONSTANT 'value_based_on_infile')

I would do this via a script, where the script command line contains the filename. The script dynamically generates a control file and places the name of the file into the NAME column as a constant.

Now this is a bit of a pain to do when dealing with the NT command language (as oppose to Unix shell scripting), but the following should give you a rough idea.

--
@echo off

rem  we expect %1 to contain the filename

rem create the controlfile
echo.load data   > dynamicload.ctl
echo.infile '%1' >>dynamicload.ctl

rem .. rest of the controlfile echo's

echo.  name CONSTANT '%1' >> dynamicload.ctl

rem .. etc ..

rem finally we do the load
sqlldr control=dynamicload.ctl

--

It will also make good sense to make the filename dynamicload.ctl an
unique one that is dynamically created. That way you make this command
script "thread safe" (i.e. if two copies of these are run at the same
time, they will overwrite the same controlfile).

--
Billy