We help IT Professionals succeed at work.

Sequence closing in the middle of sqlldr

bawinnicki
bawinnicki asked
on
I am trying to load master/detail type records with sqlldr. I am using when clauses to decipher which table to load into.  The setup looks like
Master
--detail_table1
--detail_table1

--detail_table2

So some of the details must go into different tables.  I use a sequence (sequence.nextval) to get an identifier on the Master record. Then I attempt to use sequence.currval to provide a relationship between the details and the master.  However, when I run this, the sequence.currval only inserts into the first 1 or 2 details, and then it says that the sequence has been closed.  I get ORA 08002, saying the nextval must be called first.  However, I don't understand because I have called the nextval, and it successfully uses it in the currval for the first 2 details, but then it just stops...I think the sequence is closing or something like that...
Comment
Watch Question

RMZ

Commented:
hi
before calling sequance.currval u must call sequance.nextval
try select sequance.nextval  from dual ;
to see it's value to see the sequance go to max number or not
---rmz---

Author

Commented:
I have tried this and that is not the issue.  I am calling the .nextval first.  The file comes in this order:
HDRblahblahblah
ADRblahblahblah
LINblahblahblah
LINblahblahblah
So I say
Insert into hdr table
WHEN (1:3) = 'HDR'(
id   char   sq.nextval)
WHEN (1:3) = 'ADR'(
id_relate   char   sq.currval)
WHEN (1:3) = 'LIN'(
id_relate   char   sq.currval)

And the thing is that it works FINE for the ADR record, but then it fails on the LIN record.  
RMZ

Commented:
HI
i think the best way and quick way is to create a new table and make your import using then make a curssor to insert in your table as u like and u be have more control
---rmz--
Please update and finalize this old question.

Experts, please provide closing recommendations if this is not closed by the end of this week.

Thanks,

Moondancer - EE Moderator
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Lacking timely response from bawinnicki or contributing experts, but seeing that there is some useful info here, I recommend:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
Thanks, Dan. :)

50 points refunded, item moved to our PAQ and closed at zero points.

Moondancer - EE Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.