Re: Unique sequence number per datafile using SQL Loader

<radhika.penagonda_at_gmail.com> wrote in message news:1161787225.980599.313360_at_e3g2000cwe.googlegroups.com...
> 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.
>

How about this.. Build a table that will hold one row per file you upload.. Build a sequence associated to this table On this table, build an insert trigger that is fired when you add a new row to file table
When you have a new file to upload, add a row for each new data table, and take the new id as the base for your "key"

Hope this helps!

Tony Miller
Monroe, WA Received on Thu Oct 26 2006 - 06:56:26 CEST