Load file based on line number in sqlldr

oraclesql-loader

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?

Best Answer

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
Related Question