Re: SQLLDR Question

There are really two answers to your question. First, if you don't care about referencing the next value again, you could use a sequence(max,1) operator in your sql*load control file.

However, if you want to use a sequence (an actual oracle object), you can write a before insert trigger on your table. During the load, the trigger plucks the next value from the sequence and replaces the SEQNO value. For example:

create or replace trigger BLDG_ID_LIST_tr01

   before insert on BLDG_ID_LIST
   referencing new as new old as old for each row

declare

   ct number;
begin

• get an artificial key select your_sequence_name.nextval into ct from dual; :new.SEQNO := ct; end;

A drawback to this method is that you can not use DIRECT loads because the triggers are disabled in a direct load...

amerar_at_ci.chi.il.us wrote in message <7sdrdg$inj$1_at_nnrp1.deja.com>...
>
>
>Hello,
>
>I have an Oracle table that has a composite primary key, and one of the
>fields is a sequence.
>
>I am going to populate the data in this table using SQLLDR and loading
>the data from a text file. I get this error message:
>
>SQL*Loader-923: Column SEQNO in table BLDG_ID_LIST is NOT NULL but is
>not being loaded
>
>How am I supposed to populate a sequence using SQLLDR???
>
>Thanks,
>
>Arthur
>amerar_at_ci.chi.il.us
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Sep 23 1999 - 17:48:26 CDT