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

At first sight this seems difficult to achieve, but if you are loading a limited and constant number of files, you might want to consider the following (rather tricky) solution:

1. add a column to your target table (hopefully only a staging table, not the real one) for storing record numbers.
2. after each real INFILE clause, put one INFILE loading a dummy file containing a single record whose data is easily distinguishable.
3. in the field specification section add the column mentioned above with the keyword RECNUM, so that you can recreate the original order of the records at any time.

Data coming from the first file will be that having RECNUM between 1 and the first dummy record.
The second data file will be between the first dummy record and the second one, and so on.

If you don't like all this mess, you might want to consider loading one file at a time, using a script to iterate through the list of files and running a sql script to perform whatever additional calculations on the current batch of data, passing the file name as a parameter if necessary or, alternatively, write a script hacking the data files beforehand.

Cheers,
Flavio Received on Thu Jul 03 2003 - 18:01:38 CDT