Re: SQL*LOADER and SEQUENCE values

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Sat, 11 Mar 2000 22:06:55 GMT
Message-ID: <8aeg1t$sit$1@nnrp1.deja.com>

In article <8EF49BEB9cjacksnmindspringcom_at_198.99.146.130>,   cjacksn_at_mindspring.com (Caston Jackson) wrote:
> Any ideas on how to use a sequece number that is sequenced every time
a
> group of records are imported ?
>
> i.e. if the columns in the table are as below
>
> IMPORTVAL, TEST1, TEST2
>
> every time an import of x records occurs the IMPORTVAL should be
> incremented by 1.
>
> Therefore group 1 of 1000 records would have a value of 100
> group 2 of 2000 must have a value of 101
> group 3 of 150 must have a value of 102
>
> each group is an insert of records.
>
> The problem I have found with SQL*LOADER is that you can call
> the sequence value nextval however the value will increment for each
> record. To add to the problem, calling the currval function fails
because
> the system must call the nextval first.
>
> Any ideas????
>

Use a trigger:

create table t ( importval int, test1 int, test2 int ); create sequence import_seq;

create or replace trigger t_trigger
before insert on t
for each row
declare

    seq_not_defined exception;
    pragma exception_init( seq_not_defined, -08002 ); begin

    select import_seq.currval into :new.importval from dual; exception

    when seq_not_defined then

        select import_seq.nextval into :new.importval from dual; end;
/

That'll get a nextval on the first row and use currval for the rest.

> Thanks,
>
> Caston
>
>

--

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Mar 11 2000 - 16:06:55 CST