| SQL*LOAD - master detail record [message #162304] |
Thu, 09 March 2006 11:00  |
pday@tullib.com
Messages: 11
Registered: March 2006
Location: London
|
Junior Member |
|
|
Hi
My data file has a header record and then multiple detail records.
Company,Invoice,Start date,End date,Subscriber,Currency
ABC,621822,10-Oct-2005,15-Oct-2005,LIBEBLON,CHF
Fine Date,Ref,Control NO, Amount
15-OCT-2006,143243,AAA/123,2000.89
15-OCT-2006,126543,ABA/123,2430.89
15-OCT-2006,167943,ACA/123,23420.89
Ideally I'd like to load the first detail line into one table and the rest of the detail lines into another table, the primary key of table 1 being the invoice number (note the invoice number is not referenced in the detail records).
The other way would be to store in one table replicating the header record for each detail record.
It may be this is not possible in sql*loader and I will have to reformat the file first. I have searched the ihe internet all day for this but have not had much luck.
Many thanks
|
|
|
|
| Re: SQL*LOAD - master detail record [message #162392 is a reply to message #162304] |
Fri, 10 March 2006 00:42  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
What you have here is (what I call) a Heterogeneous Context-Sensitive data file.
Heterogenous because you have 2 or more different types of rows.
Context-Sensitive because the foreign key INVOICE attribute of a detail record is dependent on the MASTER record that appears immediately above.
If you got super-tricky with triggers and package variables, this could be done in one load with SQL*Loader. BUT, nobody that comes after you will EVER understand what you have done.
By far the better solution is to parse the file and produce two Homogeneous Non-Context-Sensitive files (ie. dump the masters out to one file, and the details to another file with the invoice number appended to each row).
Then you load the master file on its own followed by the detail file in a separate load.
My preference for this type of parsing operation is Perl. I would strongly advise against loading it into a staging table and parsing with PL/SQL; this is exactly the sort of thing that PL/SQL is REALLY bad at.
_____________
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
