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.
