Re: Filenames using SQL Loader

jasonrowski_at_yahoo.com (Jason Rowski) wrote in message news:<dcb589c5.0303140753.3279ca6_at_posting.google.com>...
> Greetings,
>
> I have a daily batch job which is loading orders from 230 files. I am
> using SQLLDR to load these order files. I need to store the name of
> the filename in the column FILE_NAME in the order table IPX_ORDERS.
> How can I do this ?
>
> Thanks
> Jason

If you have a utility like awk available in a reasonable shell, assuming your fields are separation by | and you pass the filename as an argument into a script you can do something like:

#!/bin/ksh
FILENAME=$1
awk -v filename=$FILENAME ' BEGIN {FS="|"}

      { print $0 "|" filename } ' < $FILENAME > ${FILENAME}.new

Then load from sqlldr. Off the top of my head, YMMV. If you are in a Win environment, maybe you can search the web for bourne-shell-like tools. I find it real useful to be able to have a generic loader ctl file and then sed it for each file to be loaded:

• sed "s/@1_at_1/trans$$.dat/" < generic.ctl |
• sed "s/@2_at_2/transbad$$.dat/" > generic$$.ctl

LOAD DATA
INFILE '@1_at_1'
BADFILE '@2_at_2'
TRUNCATE
INTO TABLE your_table
FIELDS TERMINATED BY "|"
( column list...)

jg

--
@home.com is bogus.
It's a wicked world we live in, bunnies and guns, bunnies and guns: 
http://www.signonsandiego.com/news/uniontrib/thu/metro/news_7m13baskets.html