SQL*Loader之CASE10
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
