Re: Generate a continous sequence number across multiple SQL Loader loads

>I have a large amount of data from multiple files to be loaded into one
>database. How can I use one sequence to track and insert all this data?
>Is there any way to set the DEFAULT value to a sequence? The docs say
>that it can't be done because the columns are "not fully specified."
>Can I use a sequence that I created for that user?
>
>Thanks!!
>
>LeMee

If you are running several SQLLOAD processes simultaneously, you can put a row-insert trigger on the table that gets the value from the sequence. There is no problem with having multiple processes accessing it simultanously; a number will appear only once. (You might want to increase the cache number for the sequence). If you are using direct loading, this will not work because I believe triggers do not fire then.

The other option requires more information... if you know how many times/data files you are loading, you can use the RECORD (I think that is the name) data type in SQLLOAD. This tells the row number of that record in the data file. If you know you will load 20 data files, you could set column ID to 50*RECORD for file 1, 1+50*RECORD for the 2nd load, 2+50*RECORD for the 3rd load etc. This is contigent upon being able to calculate the value; SQLLOAD is pretty primitive with some of these.

Hope these ides help

Dan Hekimian-Williams Received on Sun Aug 08 1999 - 02:34:37 CEST