Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Loader & Oracle sequences

Re: SQL Loader & Oracle sequences

From: Henk de Wilde <dewildeh_at_xs4all.nl>
Date: 1998/04/30
Message-ID: <35489f8e.22698775@news.xs4all.nl>#1/1

On Thu, 30 Apr 1998 09:17:05 -0400, "Deborah A. Dawicki" <dawicki_at_princeton.edu> wrote:

>Having a problem with SQL*Loader and Oracle sequences. I run sqlload
>twice from a ksh script - the first load sets the sequence
>trans_header_seq.nextval in the load of a header record which works
>fine, the 2nd load sets the value of a column in a detail table to
>trans_header_seq.currval - and here's the line that is in the
>cntlcards. The error is below it.
>
> TRANS_HEADER_ID "LRADMIN.TRANS_HEADER_SEQ.CURRVAL",
>
>Record 9: Rejected - Error on table LRADMIN.TRANS_DETAIL_BT.
>ORA-08002: sequence TRANS_HEADER_SEQ.CURRVAL is not yet defined in this
>session
>
>Here's the create sequence statement:
>
>create sequence trans_header_seq
> increment by 1
> start with 10000000000
> maxvalue 99999999999
> minvalue 10000000000
> cycle;
>commit;
>
>What can I do to avoid this error?
>

The error here is more or less what the message states : your sequence is okay, but when you start your second load, you also start a new ORACLE session, don't confuse that with a ksh session, sequences have to be 'initialized' in the _current_ oracle session by at least one call to next_val.

You can create a solution to this problem if there is an identifying value that allows you to unambiguously detect at runtime which record should go to which table. For instance position [100] is always a space in the master and never in the child records. Or always a number in the one and a character in the other. Any condition will work as long as it is unambiguous.

If you can identify such a condition you can then merge both controlfiles, with two infile clauses and two 'INTO TABLE' clauses. You then add the condition in the when clause in the first into table clause and its opposite in the when clause of the other.

Of course you have to load the master file first, but the initialization of your sequence while processing the first datafile will then be preserved for the second datafile. I know this is rather involved, and for exact syntax you will have to check the 'Utility's User Guide' (chapter 6) but AFAIK there is no other solution.

I hope this helps

Henk de Wilde. Received on Thu Apr 30 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US