| How to load run time value into multiple tables using SQL Loader [message #125003] |
Wed, 22 June 2005 12:46  |
Luan-i
Messages: 3
Registered: June 2005
|
Junior Member |
|
|
Hi, I have an invoices flat file that looks like
1xxxxxxxxxxxxxxxxxxxx
2xxxxxxxxxxxxxxxxxxxx
2xxxxxxxxxxxxxxxxxxxx
The record that starts with 1 represents invoice header and should be loaded into header table. The record that starts with 2 represents invoice line and should be loaded into line table. In both tables, there is a field called "Invoice_id", which is not anywhere in the flat file.
What I am trying to accomplish is to load some unique number into the invoice_id field of the header table, and load the invoice_id that is already loaded into header, into line table. Right now, I can put the unique number into the header by using sequence but but I cannot find a way to load it into the line table.
Is there a way to accomplish this by using SQL Loader? If so, how? Thanks.
|
|
|
|
| Re: How to load run time value into multiple tables using SQL Loader [message #125009 is a reply to message #125003] |
Wed, 22 June 2005 14:24  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
First, to load into more than one table based on a condition (such as the value of column 1), check out the sqlldr documentation and look for the WHEN clause.
As for your second problem, how do you know which details go with which headers? In your example you have a row with a 1 followed by two rows with a 2. How do we know that the 2's go with the 1? And what comes next in the series?
|
|
|
|
| Re: How to load run time value into multiple tables using SQL Loader [message #125012 is a reply to message #125003] |
Wed, 22 June 2005 14:49 |
Luan-i
Messages: 3
Registered: June 2005
|
Junior Member |
|
|
The pattern is:
1xxxxx -> Header for invoice A
2xxxxx -> Line for invoice A
2xxxxx -> Line for invoice A
1xxxxx -> Header for invoice B
2xxxxx -> Line for invoice B
2xxxxx -> Line for invoice B
2xxxxx -> Line for invoice B
So the file starts with one "1" record, followed by varying numbers of "2" records. They belong to the same invoice, until the next "1" record is encountered. Each invoice has only one header, but can have multiple detail ines.
Currently I am able to load all the "1" records into the header table and al the "2" records into the detail line table. I am also able to populate unique numbers in invoice_id in the header table by using "sequence.NEXTVAL". However, I need to be able to populate the invoice_id in the detail line table so a relationship between the header table and line table can be established.
Thanks.
|
|
|
|
| Re: How to load run time value into multiple tables using SQL Loader [message #125018 is a reply to message #125003] |
Wed, 22 June 2005 15:39 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ok...I did something like this once, although the way I solved it was to write a (very trivial) perl program to take the input file in and produce two output files, one for headers and one for lines. The perl program took care of assigning the id.
Then it was a simple control file to load the two files into two tables. So that is certainly one approach.
Another might be to use the continueif clause to combine multiple physical records into a single logical record. I'm not sure exactly how that approach would work without trying it, but it would involve either RECNUM or SEQUENCE in the control file. Note that a control file sequence is different than a regular sequence object that you create in the database. If all of the line items were grouped into a single logical record per header, then they'd all have the same RECNUM or SEQUENCE identifier.
A third approach is to simply load your data exactly as it is into a staging table (or use external tables to essentially do the same thing). Then use a sql insert statement (or two) to insert the appropriate data into your appropriate tables.
|
|
|
|
|
|
| Re: How to load run time value into multiple tables using SQL Loader [message #125381 is a reply to message #125037] |
Fri, 24 June 2005 15:35 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
What you could do is write a function and use this function in your control file in both sections of the WHEN clause, which smartin mentioned.
The basic idea of the function would be to insert an Oracle sequence number (and return it) into a temporary table when column1 is a '1' and then query the table to return that value when column1 is a '2.' You could either truncate the table every time you send a '1' or just get the max sequence number. The latter could be a burden if you have many rows as you'll be doing a full table scan on every record in your data file.
I'll leave that up to the OP to decide on their own.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
