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

It doesn't look like there's sqlldr syntax to do this clean within one control file that I can run in the background...For now, I just do one control file for each infile and hardcoded the value that's infile specific. I will play with the various ideas suggested till my boss tells me to stop being creative and get work done:) Thanks!

Cynthia

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0307032351.6c5dccfc_at_posting.google.com>...
> 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).
Received on Sun Jul 06 2003 - 20:56:49 CDT