Re: SQL* Loader Question

In article <Cyx3v3.7oE_at_wtc35a.DaytonOH.NCR.COM>, Tim Wendt <tim.wendt_at_daytonoh.ncr.com> writes:
> I am having trouble figuring out if SQL*Loader control file is capable of
> loading an order file from one of our old systems into an oracle table. Bear
> with me for a second, it's not too complicated...here is the problem. The data
> resides in a flat file that is something like this:
>
> 10 10151
> 20 1234-5678 3
> 20 4321-8765 15
> 10 10163
> 20 1234-5678 7
> 20 2222-2221 2
> 20 3333-3332 43
> < there could be numerous 20 records for each 10 record >
>
> Records that start with a 10 specify an order. The order number on the first
> record is 10151.
>
> Records that follow the 10 record are order detail information and begin with a
> 20. Order 10151 has 3 of product 1234-5678 and 15 of 4321-8765. Next is order
> 10163....and so on.
>
> I want to load the data into a table like this
>
> create table order_detail (
> order_num number (5),
> product_id char (8),
> quantity_ordered number (4)
> )
>
> It seems to boil down to this...can I store the order number in a variable? Then
> read and load the detail records using this variable. Then store the next order
> number when the control file encounters a the next 10 record? and so on. I went
> thru the Utilities Unser's Manual but I couldn't put my finger on syntax that
> would do this.
>
> Thanks in advance.
>
> Tim Wendt
> AT&T Global Information Solutions
> tim.wendt_at_daytonoh.ncr.com
>

Don't know if this would work for you, but you could try using the CONCATENATE and WHEN clauses. Concatenate the header records and detail records into one long logical record. Use the WHEN clause to break the resulting logical record into the individual rows that you want to create.

Problems:

1. You have to know the maximum number of detail records per header, and hardcode that many WHEN clauses in your control file.
2. Be very aware of spacing at the end of your data records. I think that that spacing is maintained during concatenation.

Good luck!

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285