Re: SQL*LOADER & SEQUENCE VALUES

As long as your not using DIRECT Path, i.e. DIRECT=TRUE in the sqlload command, you may be able to implement this type of logic in a after Insert trigger (if the col is not PK) or before insert trigger (if col is pk). Something like,

Store the block of key numbers you want to use in a system parm table,

CREATE Table SYSPARM

 ( Seq_to_reuse NUBMER,  Value you want to use repetitively. 
   Seq_cnt NUMBER,       Upper limit of seq_to_reuse 
   Seq_block_cnt NUMBER  Count of seq_to_reuse used

• You may want to create a function to perform following logic
  • Get seq_to_reuse, which check seq_cnt used, and against seq_block_cnt determines whether to used seq_to_reuse) or increment seq_to_resue by seq+_block+_cnt.

so --

CREATE TRIGGER ON tablename BEFORE INSERT FOR EACH ROW.     NEW.IMPORTVAL := my_function() which returns seq_to_reuse.

This is psuedo only (obviously) .

n.b. any trigger firing will impact sqlloader performance,

Other suggestions, I usually only use sqlloader to stage data, all other modifications can be made (dml) against table once loaded,

i.e. drop pk constaint (to allow nulls into pk) then

sqlload, then run procedure or function to update IMPORTVAL

add pk constraint back against loaded (or staged) table.

Chris Monte
PineCone Software, Inc.
Oracle Consultants.
qualis_at_ccnet.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????
>
> Thanks,
>
> Caston
Received on Sun Mar 12 2000 - 02:16:20 CET