Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> sqlldr question

sqlldr question

From: Davey, Alan <Alan.Davey_at_encodasystems.com>
Date: Tue, 7 Dec 2004 13:24:10 -0600
Message-ID: <746DCE6FEC476842B2229E15EBFC1B9E302E09@mempexc0013.encodasystems.net>

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."

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 07 2004 - 13:29:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US