| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: sequences and loading from sqlldr
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
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.
>
>
 |
 |