Re: Unique sequence number per datafile using SQL Loader

In article <1161787225.980599.313360_at_e3g2000cwe.googlegroups.com>, radhika.penagonda_at_gmail.com says...
> Here's the requirement:
>
> I have a datafile with say 500 records to be loaded. Each time I load
> the file, I need to generate a sequence number unique to the file,
> which means all the 500 records loaded from that file should have the
> same sequence number.
> The next file to be laoded will have another unique number.
> I thought of using to_number(to_char(sysdate, 'YYYYMMDDHHMMSS') for a
> unique sequence. however, this does not generate one unique number for
> a file.
>
> Is there any other way to handle this requirement in the control file
> itself.
>
>

[Quoted] Build a table exactly like the target table. Include a column for the sequence number. Load the data into that table. Find the next seq number by selecting the highest seq number from the target table and add 1 (one) to it. Put the seq number value into the load table's seq number. Transfer the data from the from the load table to the target table.

Alternatively, Load the data into to target. Have a column for the seq number in that table. It is null after the load finishes. Find the highest seq value from the target table, add one to it, update the null rows to the seq number. Received on Wed Nov 15 2006 - 05:43:04 CET