CASE10
1. SQL脚本
[oracle@node3 ulcase]$ cat ulcase10.sql
rem host write sys$output "Building case 10 demonstration tables. Please wait"
rem do all cleanup
drop table orders;
drop table customers;
drop type item_list_type;
drop type item_type;
drop type customer_type;
rem Create an ORDER record that has a VARRAY for the items that comprise the
rem order and has a reference field to a record in the CUSTOMER table for
rem the customer placing the order.
rem create customer type
create type customer_type as object (
cust_no char(5),
name char(20),
addr char(20)
);
/
rem create object table for customer type
create table customers of customer_type
(primary key (cust_no))
object id primary key;
rem create type for order items
create type item_type as object (
item varchar(50),
cnt number,
price number(7,2)
);
/
rem create varray type for order items
create type item_list_type as varray (1000) of item_type;
/
rem create orders table with varray for items and ref to object table
create table orders (
order_no char(5),
cust ref customer_type references customers,
item_list item_list_type
);
exit;
/
2. 控制文件
[oracle@node3 ulcase]$ cat ulcase10.ctl
-- Copyright (c) 1991, 2004 Oracle. All rights reserved.
-- NAME
-- ulcase10.ctl - SQL*Loader Case Study 10: Loading REF Fields and VARRAYs
--
-- DESCRIPTION
-- This case study demonstrates the following:
--
-- Loading a customer table that has a primary key as its OID and
-- stores order items in a VARRAY.
--
-- Loading an order table that has a reference to the customer table and
-- the order items in a VARRAY.
--
-- TO RUN THIS CASE STUDY:
-- 1. Before executing this control file, log in to SQL*Plus as
-- scott/tiger. Enter @ulcase10 to execute the SQL script for
-- this case study. This prepares and populates tables and
-- then returns you to the system prompt.
--
-- 2. At the system prompt, invoke the case study as follows:
-- sqlldr USERID=scott/tiger CONTROL=ulcase1.ctl0 LOG=ulcase10.log
--
-- NOTES ABOUT THIS CONTROL FILE
-- cust_no and item_list_count are FILLER fields. The FILLER field is
-- assigned values from the data field to which it is mapped.
--
-- The cust field is created as a REF field.
--
-- item_list is stored in a VARRAY.
--
-- The second occurrence of item_list identifies the datatype of each
-- element of the VARRAY. Here, the datatype is COLUMN OBJECT.
--
-- The listing of item, cnt, price shows all attributes of the column
-- object that are loaded for the VARRAY. The list is enclosed in parentheses.
--
-- The data is contained in the control file and is preceded by the
-- BEGINDATA parameter.
--
LOAD DATA
INFILE *
CONTINUEIF THIS (1) = '*'
INTO TABLE customers
REPLACE
FIELDS TERMINATED BY ","
(
cust_no CHAR,
name CHAR,
addr CHAR
)
INTO TABLE orders
REPLACE
FIELDS TERMINATED BY ","
(
order_no CHAR,
cust_no FILLER CHAR,
cust REF (CONSTANT 'CUSTOMERS', cust_no),
item_list_count FILLER CHAR,
item_list VARRAY COUNT (item_list_count)
(
item_list COLUMN OBJECT
(
item CHAR,
cnt CHAR,
price CHAR
)
)
)
BEGINDATA
*00001,Spacely Sprockets,15 Space Way,
*00101,00001,2,
*Sprocket clips, 10000, .01,
Sprocket cleaner, 10, 14.00
*00002,Cogswell Cogs,12 Cogswell Lane,
*00100,00002,4,
*one quarter inch cogs,1000,.02,
*one half inch cog, 150, .04,
*one inch cog, 75, .10,
Custom coffee mugs, 10, 2.50
执行后结果：
[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase10.sql
[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase10.ctl
SQL> select * from customers;
CUST_ NAME ADDR
----- -------------------- --------------------
00001 Spacely Sprockets 15 Space Way
00002 Cogswell Cogs 12 Cogswell Lane
SQL> desc orders
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_NO CHAR(5)
CUST REF OF CUSTOMER_TYPE
ITEM_LIST ITEM_LIST_TYPE
SQL> select order_no from orders;
ORDER
-----
00101
00100
SQL> select cust from orders;
CUST
--------------------------------------------------------------------------------
00003C038A0038035D767AE692347EE050A8C00D0209980000001726010001000100290000000000
0C0100050100002A00078401FE0000000D053030303031
00003C038A0038035D767AE692347EE050A8C00D0209980000001726010001000100290000000000
0C0100050100002A00078401FE0000000D053030303032
SQL> select item_list from orders;
ITEM_LIST(ITEM, CNT, PRICE)
--------------------------------------------------------------------------------
ITEM_LIST_TYPE(ITEM_TYPE('Sprocket clips', 10000, .01), ITEM_TYPE('Sprocket clea
ner', 10, 14))
ITEM_LIST_TYPE(ITEM_TYPE('one quarter inch cogs', 1000, .02), ITEM_TYPE('one hal
f inch cog', 150, .04), ITEM_TYPE('one inch cog', 75, .1), ITEM_TYPE('Custom cof
fee mugs', 10, 2.5))
查看日志文件：
[oracle@node3 ulcase]$ cat ulcase10.log
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 04:02:40 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: ulcase10.ctl
Data File: ulcase10.ctl
Bad File: ulcase10.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: 1:1 = 0X2a(character '*'), in current physical record
Path used: Conventional
Table CUSTOMERS, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CUST_NO FIRST * , CHARACTER
NAME NEXT * , CHARACTER
ADDR NEXT * , CHARACTER
Table ORDERS, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ORDER_NO NEXT * , CHARACTER
CUST_NO NEXT * , CHARACTER
(FILLER FIELD)
CUST DERIVED REF
Arguments are:
CONSTANT 'CUSTOMERS'
CUST_NO
ITEM_LIST_COUNT NEXT * , CHARACTER
(FILLER FIELD)
ITEM_LIST DERIVED * VARRAY
Count for VARRAY
ITEM_LIST_COUNT
*** Fields in ITEM_LIST
ITEM_LIST DERIVED * COLUMN OBJECT
*** Fields in ITEM_LIST.ITEM_LIST
ITEM FIRST * , CHARACTER
CNT NEXT * , CHARACTER
PRICE NEXT * , CHARACTER
*** End of fields in ITEM_LIST.ITEM_LIST
*** End of fields in ITEM_LIST
Table CUSTOMERS:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table ORDERS:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 149120 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Sep 19 04:02:40 2014
Run ended on Fri Sep 19 04:02:41 2014
Elapsed time was: 00:00:01.33
CPU time was: 00:00:00.23