sqlldr question
- From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 7 Dec 2004 13:24:10 -0600
Hi,
I have a datafile where each line can be a different type of record.
I'm trying to load each of these records into an appropriate table and
maintain the parent/child hierarchy. =0D
My control file looks like:
LOAD DATA
INFILE 'B0271235.dat'
APPEND
INTO TABLE TEST_AGENCY
when (recno =3D '21')
(recno filler position(1) char terminated by ';',
id integer external terminated by ';',
name char terminated by ';',
filename constant 'B0271235.DAT',
agency_id "seq_test_agency.nextval"
)
INTO TABLE TEST_STATION
WHEN (recno =3D '22')
(recno filler position(1) char terminated by ';',
call_letters char terminated by ';',
media_type char terminated by ';',
agency_id "seq_test_agency.currval",
station_id "seq_test_station.nextval"
)
INTO TABLE TEST_PAYEE
WHEN (recno =3D '23')
(recno filler position(1) char terminated by ';',
name char terminated by ';',
address1 char terminated by ';',
station_id "seq_test_station.currval",
payee_id "seq_test_payee.nextval"
)
And the datafile will look like:
21; <agency record fields here>;
22; <station record fields here>;
23; <payee record fields here>; =0D
<31>; <another record type>;
<32>; <another record type>;
<...>; <...>;
Note that there may be multiple payee children for a given station
parent. However, in this particular datafile, I have 42 stations and 42
payees (1:1). When I use conventional path load, I see two distinct
values of station_id in the payee table instead of 42 distinct
station_ids. I'm assuming that this is because of the way sql*loader
makes use of the bind array to insert records into a table? If I happen
to add the "rows=3D1" option to sqlldr, then all of the records get
inserted correctly. Is there a way to load these parent/child records
with the correct parent_id and NOT set rows=3D1? Eventually, I will be
loading the other record types into other child tables, but I wanted to
nail this with a simple 3 table hierarchy before I run out of coffee.
On a whim, I tried to use direct path, but that had the effect of
leaving the sequence columns as NULL even though they are marked as not
null. This is using sqlldr version 10.1.0.2 client hitting an Oracle
10.1.0.3 database, each of which is running on the Windows platform.
Thanks,
-----------------------------------------
Alan Davey
Programmer/Analyst
Harris Corporation, formerly Encoda Systems
w) 212.295.3458
"The information in this e-mail is intended solely=0D
for the addressee and may contain information=0D
which is confidential or privileged. Access to this
e-mail by anyone else is unauthorized. If you=0D
are not the intended recipient, or believe that=0D
you have received this communication in error,=0D
please do not print, copy, retransmit,=0D
disseminate, or otherwise use the information.=0D
Also, please notify the sender that you have=0D
received this e-mail in error, and delete the=0D
copy you received."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
