SQL* Loader Question

From: Tim Wendt <tim.wendt_at_daytonoh.ncr.com>
Date: Mon, 7 Nov 1994 22:01:51 GMT
Message-ID: <Cyx3v3.7oE_at_wtc35a.DaytonOH.NCR.COM>


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



Hmmm....beer! - Homer
Received on Mon Nov 07 1994 - 23:01:51 CET

Original text of this message