Re: sequences and loading from sqlldr

I haven't had time to try this but it looks pretty cool ... intuitively I say to myself 'of course there's a way'.

Anyway as an aside this probably wouldn't work with a direct path load, which is important for fast data loads.

As such in the application I am working on now we forget the sequence altogether and select max(value) out of the table before we format the loader file. That works ..... and is old school. Kind of like sybase. It will work with parallel loads also.

-bdw

In article <395297EF.3479E4C9_at_hotmail.com>,   kskasi_at_hotmail.com wrote:
> Hi guys
>
> I think you are missing something here. We can actualluy use Oracle
 sequence
> in SQL loader.
> Check the following examples
> Example-I:
> =========
>
> In the first example, all of the fields are located in the datafile
> based on position, which makes this easier. Another example below
 covers
> data that
> is comma delimited.
>
> We want to load the data into the following table:
>
> SQL> CREATE TABLE load_db_seq_positional
> 2 (seq_number NUMBER,
> 3 data1 NUMBER,
> 4 data2 CHAR(15) );
>
> We will use the following sequence:
>
> SQL> CREATE SEQUENCE db_seq
> 2 START WITH 1
> 3 INCREMENT BY 1;
>
> The control file would look like:
>
> LOAD DATA
> INFILE *
> INTO TABLE load_db_seq_positional
> (seq_number "db_seq.nextval"
> data1 POSITION(1:5),
> data2 POSITION(6:15),
> )
> BEGINDATA
> 11111AAAAAAAAAA
> 22222BBBBBBBBBB
>
> After we run SQL*Loader, our table data looks like:
>
> SQL> SELECT * FROM load_db_seq_positional;
>
> SEQ_NUMBER DATA1 DATA2
> ---------- ---------- ---------------
> 1 11111 AAAAAAAAAA
> 2 22222 BBBBBBBBBB
>
> Example-II:
> ==========
>
> In this example, the data fields are comma delimited. The key here is
> that since fields are delimited, SQL*Loader will expect to find
 values for
> the field SEQ_NUMBER in the data file. Since such entries do not
 exist,
> what we must do is to put the SEQ_NUMBER field as the last field in
 the
> control file, and then use the TRAILING NULLCOLS clause to indicate to
> Loader
> that on some lines (in this case all), there may be "trailing
 columns" which
>
> are null, or non-existent.
>
> Here is the similar create table statetement, we will use the same
> sequence:
>
> SQL> CREATE TABLE load_db_seq_delimited
> 2 (seq_number NUMBER,
> 3 data1 NUMBER,
> 4 data2 CHAR(15));
>
> The control file would look like:
>
> LOAD DATA
> INFILE *
> INTO TABLE load_db_seq_delimited
> FIELDS TERMINATED BY ","
> TRAILING NULLCOLS
> (data1,
> data2,
> seq_number "db_seq.nextval"
> )
> BEGINDATA
> 11111,AAAAAAAAAA
> 22222,BBBBBBBBBB
>
> cheers...kasi
>
> ddf_dba_at_my-deja.com wrote:
>
> > 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.
  >
  >
  

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