一 描述

Case Study 10: Loading REF Fields and VARRAYs

Case 10 demonstrates:

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

二 操作环境

OS info

windowsserver2003 32bit

DB info

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  9.0.1.1.1       Production
Oracle9i Enterprise Edition                             9.0.1.1.1       Production
PL/SQL                                                  9.0.1.1.1       Production
TNS for 32-bit Windows:                                 9.0.1.1.0       Production

SQL>

other

目录结构:C:\oracle\product\9.2.0\db_1\rdbms\demo

三 过程设计

1.准备的源flat数据
*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

2.相关文件ulcase10.sql
rem
Rem $Header: ulcase10.sql 11-may-00.13:48:45 rpfau Exp $
rem
rem ulcase10.sql
rem
rem Copyright (c) 1998, 1999,, 2000 Oracle Corporation.  All rights reserved.
rem
rem    NAME
rem      ulcase10.sql - set up for SQL Loader example 10
rem
rem    DESCRIPTION
rem      Create tables need to for example of using SQL Loader to load
rem      VARRAYS and references
rem
rem    NOTES
rem      none
rem
rem    MODIFIED   (MM/DD/YY)
Rem    rpfau       05/11/00 - Remove slashes on create table commands so it
Rem                  runs successfully using sqlplus.
Rem    cmlim       07/27/99 - add /
rem    mjaeger     07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem    jstenois    06/17/99 - cleanup tables before load and show feedback
rem    jstenois    10/26/98 - demo of 8.1 features for sqlldr
rem    jstenois    10/26/98 - Created
rem

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

3.相关文件ulcase10.ctl
-- Inital load of CUSTOMERS table and ORDERS table. Load one customer record
-- and one order record for the customer
--
-- This example shows how to load a REF field and a VARRAY.  The ORDER table
-- has a primary key reference to the customer table and it stores the order
-- items in a VARRAY.
--

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


4.以scott用户连接数据库
5.scott用户运行ulcase10.sql初始化环境
6.在cmd命令下执行sqlldr加载ulcase10.ctl控制文件命令
7.查看sqlldr日志信息
8.查看sqlldr bad文件信息
9.查看数据库加载成功的数据

四 详细步骤操作

1.以scott用户连接数据库
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Aug 8 12:38:05 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL>

2.scott用户运行ulcase10.sql初始化环境
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Aug 8 12:38:05 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> @ulcase10
drop table orders
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table customers
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop type item_list_type
*
ERROR at line 1:
ORA-04043: object ITEM_LIST_TYPE does not exist


drop type item_type
*
ERROR at line 1:
ORA-04043: object ITEM_TYPE does not exist


drop type customer_type
*
ERROR at line 1:
ORA-04043: object CUSTOMER_TYPE does not exist



Type created.


Table created.


Type created.


Type created.


Table created.

Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\oracle\product\9.2.0\db_1\rdbms\demo>

3.在cmd命令下执行sqlldr加载ulcase10.ctl控制文件命令
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr "scott/tiger" control=ulcase10.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Wed Aug 8 12:40:20 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

C:\oracle\product\9.2.0\db_1\rdbms\demo>

4.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Wed Aug 8 12:40:20 2012

(c) Copyright 2001 Oracle Corporation.  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 Wed Aug 08 12:40:20 2012
Run ended on Wed Aug 08 12:40:21 2012

Elapsed time was:     00:00:01.46
CPU time was:         00:00:00.09   

5.查看sqlldr bad文件信息
(注:无)
6.查看数据库加载成功的数据
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Aug 8 12:43:52 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select * from orders;

ORDER
-----
CUST
--------------------------------------------------------------------------------
ITEM_LIST(ITEM, CNT, PRICE)
--------------------------------------------------------------------------------
00101
00003C038A00385D12A49FECD1483296D3FE94959FECAF0000001726010001000100290000000000
0C0100050100002A00078401FE0000000D053030303031
ITEM_LIST_TYPE(ITEM_TYPE('Sprocket clips', 10000, .01), ITEM_TYPE('Sprocket clea
ner', 10, 14))

00100

ORDER
-----
CUST
--------------------------------------------------------------------------------
ITEM_LIST(ITEM, CNT, PRICE)
--------------------------------------------------------------------------------
00003C038A00385D12A49FECD1483296D3FE94959FECAF0000001726010001000100290000000000
0C0100050100002A00078401FE0000000D053030303032
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> select * from customers;

CUST_ NAME                 ADDR
----- -------------------- --------------------
00001 Spacely Sprockets    15 Space Way
00002 Cogswell Cogs        12 Cogswell Lane

SQL>

五 个人总结

六 资料参考引用

Oracle9i Database Utilities
Release 1 (9.0.1)
Part Number A90192-01