rem host write sys$output "Building case 10 demonstration tables.  Please wait"rem do all cleanupdrop 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 therem order and has a reference field to a record in the CUSTOMER table forrem the customer placing the order.rem create customer typecreate type customer_type as object (  cust_no   char(5),  name      char(20),  addr      char(20));/rem create object table for customer typecreate table customers of customer_type        (primary key (cust_no))        object id primary key;rem create type for order itemscreate type item_type as object (  item      varchar(50),  cnt       number,  price     number(7,2));/rem create varray type for order itemscreate type item_list_type as varray (1000) of item_type;/rem create orders table with varray for items and ref to object tablecreate table orders (  order_no      char(5),  cust          ref customer_type references customers,  item_list     item_list_type);exit;/

-- 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 DATAINFILE * CONTINUEIF THIS (1) = ‘*‘INTO TABLE customersREPLACEFIELDS TERMINATED BY ","(  cust_no                       CHAR,  name                          CHAR,  addr                          CHAR)INTO TABLE ordersREPLACEFIELDS 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

SQL> select * from customers;CUST_ NAME           ADDR----- -------------------- --------------------00001 Spacely Sprockets    15 Space Way00002 Cogswell Cogs       12 Cogswell LaneSQL> desc orders Name                       Null?    Type ----------------------------------------- -------- ---------------------------- ORDER_NO                        CHAR(5) CUST                            REF OF CUSTOMER_TYPE ITEM_LIST                        ITEM_LIST_TYPESQL> select order_no from orders;ORDER-----0010100100SQL> select cust from orders;CUST--------------------------------------------------------------------------------00003C038A0038035D767AE692347EE050A8C00D02099800000017260100010001002900000000000C0100050100002A00078401FE0000000D05303030303100003C038A0038035D767AE692347EE050A8C00D02099800000017260100010001002900000000000C0100050100002A00078401FE0000000D053030303032SQL> select item_list from orders;ITEM_LIST(ITEM, CNT, PRICE)--------------------------------------------------------------------------------ITEM_LIST_TYPE(ITEM_TYPE(‘Sprocket clips‘, 10000, .01), ITEM_TYPE(‘Sprocket cleaner‘, 10, 14))ITEM_LIST_TYPE(ITEM_TYPE(‘one quarter inch cogs‘, 1000, .02), ITEM_TYPE(‘one half inch cog‘, 150, .04), ITEM_TYPE(‘one inch cog‘, 75, .1), ITEM_TYPE(‘Custom coffee mugs‘, 10, 2.5))

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 04:02:40 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Control File:   ulcase10.ctlData File:      ulcase10.ctl  Bad File:     ulcase10.bad  Discard File:  none specified  (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     64 rows, maximum of 256000 bytesContinuation:   1:1 = 0X2a(character ‘*‘), in current physical recordPath used:      ConventionalTable 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_NOITEM_LIST_COUNT                      NEXT     *   ,       CHARACTER              (FILLER FIELD)ITEM_LIST                         DERIVED     *           VARRAY                   Count for VARRAY        ITEM_LIST_COUNT*** Fields in ITEM_LISTITEM_LIST                         DERIVED     *           COLUMN OBJECT        *** Fields in ITEM_LIST.ITEM_LISTITEM                                FIRST     *   ,       CHARACTER            CNT                                  NEXT     *   ,       CHARACTER            PRICE                                NEXT     *   ,       CHARACTER            *** End of fields in ITEM_LIST.ITEM_LIST*** End of fields in ITEM_LISTTable 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: 1048576Total logical records skipped:          0Total logical records read:             2Total logical records rejected:         0Total logical records discarded:        0Run began on Fri Sep 19 04:02:40 2014Run ended on Fri Sep 19 04:02:41 2014Elapsed time was:     00:00:01.33CPU time was:         00:00:00.23