Re: sequences and loading from sqlldr

In article <8itimj$d6v$1_at_nnrp1.deja.com>,   ddf_dba_at_my-deja.com wrote:
> 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

I wonder if this can be a quick and dirty solution: after loading data, do a lot of select that_sequence.nextval from dual; in SQL*Plus till you get the number 2615 and stop. Since this is tedious, build a PL/SQL loop for this purpose.

--
Yong Huang

(yong321_at_yahoo.com)
(http://www.stormloader.com/yonghuang/)


Sent via Deja.com http://www.deja.com/
Before you buy.