Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sequences and loading from sqlldr

Re: sequences and loading from sqlldr

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/22
Message-ID: <8itimj$d6v$1@nnrp1.deja.com>#1/1

In article <8itf83$aa0$1_at_nnrp1.deja.com>,   orauser_at_my-deja.com wrote:
> I want to load data from a text file, into a table
> that contains a sequence.
>
> I am using sqlldr to load this data into a temp table
> and then doing an insert from this temp table into
> my database, invoking the sequence in the process.
>
> Is this necessary? Any better way to do this?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

If you are using the sequence for other processing other than bulk loads there really is no other way. SQL*Loader provides the SEQUENCE syntax but that is independent of any sequence built in the instance -- any sequence number generated through SQL*Loader would not be reflected in the database sequence. As an example let's say you're running an application where users are inserting new rows into the table and you then need to load a bulk file of additional records. Using the SEQUENCE syntax of SQL*Loader the column would be filled with properly generated values, but the created sequence used by the application remains untouched during the load:

Max sequence value in table: 115
SQL*Loader loads 2500 records
Max sequence value in table after load: 2615 Next value in sequence: 116

You COULD drop the current sequence, load the data with SQL*Loader's SEQUENCE generator then rebuild the sequence starting with 1 greater than the maximum sequenced column value, but that may be too much work for this task. I don't believe you are going to find a quick and easy solution to this situation.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Jun 22 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US