SQLLDR Case Study 1: Loading Variable-Length Data

一 描述

Case Study 1: Loading Variable-Length Data: Loads stream format records in which the fields are terminated by commas and may be enclosed by quotation marks. The data is found at the end of the control file.

Case 1 demonstrates:

• A simple control file identifying one table and three columns to be loaded.

• Including data to be loaded from the control file itself, so there is no separate datafile.
  

• Loading data in stream format, with both types of delimited fields: terminated and enclosed.
  

二 操作环境

OS+DB: windows server 2003  + 9.0.1
脚本目录:C:\oracle\product\9.2.0\db_1\rdbms\demo

三 过程设计

1. 相关文件ulcase1.sql
rem
rem $Header: ulcase1.sql 14-jul-99.14:22:19 mjaeger Exp $
rem
rem Copyright (c) 1991, 1999, Oracle Corporation.  All rights reserved.
rem
rem    NAME
rem      ulcase1.sql -
rem    DESCRIPTION
rem     
rem    RETURNS
rem
rem    NOTES
rem     
rem    MODIFIED   (MM/DD/YY)
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     ksudarsh   03/01/93 -  comment out vms specific host command
rem     ksudarsh   12/29/92 -  Creation
rem     cheigham   08/28/91 -  Creation
rem

set termout off

rem host write sys$output "Building first demonstration tables.  Please wait"

drop table emp;
drop table dept;

create table emp
       (empno number(4) not null,
        ename char(10),
        job char(9),
        mgr number(4),
        hiredate date,
        sal number(7,2),
        comm number(7,2),
        deptno number(2));

create table dept
       (deptno number(2),
        dname char(14) ,
        loc char(13) ) ;

exit

2.相关文件ulcase1.ctl
-- Copyright (c) 1991 by Oracle Corporation
--   NAME
--     ulcase1.ctl -
--   DESCRIPTION
--    
--   RETURNS
--
--   NOTES
--    
--   MODIFIED   (MM/DD/YY)
--    cheigham   08/28/91 -  Creation
--
-- $Header: ulcase1.ctl,v 1.1 1991/09/02 14:50:39 CHEIGHAM Stab $ case1.ctl
--
LOAD DATA
INFILE *
INTO TABLE DEPT   
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"    
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"

四 详细步骤操作

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 星期五 8月 3 23:47:09 2012

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


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

SQL> @ulcase1
从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>

2.在cmd环境执行sqlldr命令加载ulcase1.ctl控制文件.

C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr userid=scott/tiger control=ulcase1.ctl log=ulcase1.log

SQL*Loader: Release 9.0.1.1.1 - Production on 星期五 8月 3 23:51:38 2012

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

达到提交点，逻辑记录计数7

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

3.查看ulcase1.log日志内容.

SQL*Loader: Release 9.0.1.1.1 - Production on 星期五 8月 3 23:51:38 2012

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

控制文件: ulcase1.ctl
数据文件: ulcase1.ctl
错误文件: ulcase1.bad
废弃文件: 未作指定
:
(可废弃所有记录)

加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行，最大 256000 字节
继续:    未作指定
所用路径:       常规

表DEPT
已加载从每个逻辑记录
插入选项对此表INSERT生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *    , O (") CHARACTER           
DNAME                                NEXT     *    , O (") CHARACTER           
LOC                                  NEXT     *    , O (") CHARACTER           


表DEPT:
7 行加载成功
由于数据错误, 0 行没有加载。
由于所有 WHEN 子句失败, 0 行没有加载。
由于所有字段都为空的, 0 行没有加载。


为结合数组分配的空间:    49536字节（64行）
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:        0
读取的逻辑记录总数:        7
拒绝的逻辑记录总数:        0
废弃的逻辑记录总数:        0

从星期五 8月  03 23:51:38 2012开始运行
在星期五 8月  03 23:51:40 2012处运行结束

经过时间为: 00: 00: 01.57
CPU 时间为: 00: 00: 00.01(可?

4.查看加载到scott用户下dept表的内容.
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期五 8月 3 23:55:00 2012

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


连接到:
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 dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        12 RESEARCH       SARATOGA
        10 ACCOUNTING     CLEVELAND
        11 ART            SALEM
        13 FINANCE        BOSTON
        21 SALES          PHILA.
        22 SALES          ROCHESTER
        42 INT'L          SAN FRAN

已选择7行。

SQL>

五 个人总结

此为oracle 官方文档utilities中的关于sqlloader的case1 study. 相对简单.注意控制文件中 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 的使用方法.

六 资料参考引用

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-739805/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-739805/