SQLLDR - ID in header rec only
- From: "Knight, Jon" <jknight@xxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Apr 2005 15:44:22 -0500
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 -----
--
//www.freelists.org/webpage/oracle-l
Other related posts:
