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;

/

-- 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

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))

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