In article <8itl83$ffn$1_at_nnrp1.deja.com>,
yong321_at_yahoo.com wrote:
> 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.
>
You CAN do that, but there is a tendency to overshoot the maximum
sequence value which would then create 'holes' in the sequence numbers.
I have had to do this in the past and I have never had the PL/SQL loop
terminate where it should; it's always one greater than necessary.
--
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
