RE: SQLLDR - ID in header rec only

Right now, we're loading the whole file into a staging table, then processing the records with PL/SQL. But generating a new source file, and then SQL Loading, may be even faster. Thanks for the suggestion.

Thanks,
Jon Knight

 -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khedr, Waleed

Sent:	Tuesday, April 19, 2005 4:28 PM
To:	oracle-l_at_freelists.org
Subject:	RE: SQLLDR - ID in header rec only

Either fix the file to include the id for detail records, or use conventional load, create trigger on the master table to memorize the id in a package variable that gets read by another trigger for the detail data to populate the id in the child table. Will be slow this way.
Best way small C or Perl program to read and generate a new source file.

Regards,

Waleed

-----Original Message-----
From: Knight, Jon [mailto:jknight_at_concordefs.com]=20 Sent: Tuesday, April 19, 2005 4:44 PM
To: 'oracle-l_at_freelists.org'
Subject: 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 =20 **Samuel =20 **Cindy =20 **Sandy =20 2 **King =20 **Camden =20 **Cody =20 **Kim =20 **Courtney =20 3 **Johnson =20 **Jason =20 **Jennifer =20
• EOF -----
• SQL Loader CTL file ----- load data infile 'data.txt' replace continueif next (1:2) =3D '**' 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l