1

I am trying to load header and detail records from a file. The only way I know the difference between the header and the detail records is the line number. The first line is always the header and the other lines are detail records.

How can I specify the line number of a file when doing a conditional load? I would like to do something like:

LOAD DATA 
APPEND INTO TABLE x
WHEN [line_number] = 1
([mapping for header line])
WHEN [line_number] > 1
([mapping for detail lines])

The only reason I ask is b/c I take the primary key from the header record in the database and insert it into each detail record. That is a sequence that doesn't get populated until the insert into the header table. I have to load the header/detail in the same transaction because I can't call currval on the sequence until I call nextval - which is only called in the header insert.

Is there a way for me to do this?

Improve this question
6
  • Do you actually need to do something with the header? Does the data load depend on what you did with the headeR?
    – Mat
    Commented Apr 22, 2015 at 8:07
  • No the load doesn't depend on what I do with the header, I just need different mappings for the header and detail but in the same control file
    – Raja
    Commented Apr 22, 2015 at 11:56
  • You'll probably need two control files then, one for the header (I think there's a LINES option to limit number of lines processed), then Phil's SKIP option for the data load.
    – Mat
    Commented Apr 22, 2015 at 12:13
  • Right, I did have 2 control files. The header had: OPTIONS(direct=false, LOAD=1) for the header line and the detail control file had: OPTIONS(direct=false, SKIP=1) The problem is, I need a sequence that populates the primary key in the header table to be used in the detail table (so I can map header to details). I call the sequence.NextVal when loading the header but I can't call sequence.CurrVal when I load the details b/c I'm in a separate sqlldr session...that's why I'm aiming to load both the header/detail in one control file
    – Raja
    Commented Apr 22, 2015 at 12:40
  • Ah, I see. No idea if that's doable. You should edit those details into the question, that's rather important information.
    – Mat
    Commented Apr 22, 2015 at 13:47

1 Answer 1

Reset to default
1

skip=N is what you're looking for. Skips the first N rows, so in your case you just want to skip the first. 

OPTIONS(skip=1)
LOAD DATA
... rest of normal control file
Improve this answer
1
  • I tried that, but I don't want to skip the header, I just want to have a different mapping for the header and a different one for the details based on the line number. And in the same control file :\
    – Raja
    Commented Apr 22, 2015 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.