SQLLDR - ID in header rec only

All,
  I'm stumped. We're trying to load some data that we receive in the following format:

Header Record 1
Header Record 2
Detail Record 1
Detail Record 2
...
Detail Record N
Trailer Record

  Our first thought was to use multiple "insert into" clauses in the .ctl file to insert the header records and detail records into separate tables. Unfortunately, the detail records don't contain the header ID! Next we tried the CONTINUEIF clause to concatenate into one logical record so all the subsequent "insert into" clauses would have access to the header ID. Below is a script that demonstrates our efforts. This works as expected, however our particular file may potentially contain thousands of detail records for each header/trailer. So the .ctl file would get rather unwieldy. Even if we could get a huge .ctl file to work, we would still have the performance hit of discarding all those empty records from the multiple "insert into" clauses on short logical records.

  What seems ideal is to skip the CONTINUEIF clause and find some way to ask SQLLDR to remember the ID from the header record and use it while inserting each detail record, until reaching a trailer or a new header record. But, my research has failed to discover any such a construct. It's probably right there in front of me, so whoever can see it gets to smear egg on my face.

  Any suggestions are welcome. Thanks for looking.

Thanks,
Jon Knight

create table family (
  id number(10)
 ,last_name varchar2(30)
)
/

create table family_members (
  fam_id number(10)
 ,first_name varchar2(30)
)
/

• Data File ----- 1
  **Smith
  **Samuel
  **Cindy
  **Sandy
  2
  **King
  **Camden
  **Cody
  **Kim
  **Courtney
  3
  **Johnson
  **Jason
  **Jennifer
  
• EOF -----
• SQL Loader CTL file ----- load data infile 'data.txt' replace continueif next (1:2) = '**' into table family ( id position( 1:10) char ,last_name position(11:20) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(21:30) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(31:40) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(41:50) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(51:60) char ) into table family_members ( fam_id position( 1:10) char ,first_name position(61:70) char )
• EOF -----

--
http://www.freelists.org/webpage/oracle-l