一 描述

此档是sqlloader学习的第8篇.将多行合并为一行记录.此档使用的方式为使用 continueif 参数将多行并为一行.注意continueif this使用时,对齐数据的丢失问题.

二 操作环境

OS info

windows server 2003 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:\sqlloader_exec\ppt_case8

三 过程设计

1.准备的flat源数据.
-10,Sales,Virginia,
1/5/2000
-20,Sales,
Virginia,1/5/2000
-30,
Sales,Virginia,1/5/2000
-40,
Finance,Virginia,13/04/2001

2.相关文件ppt_case8.sql
rem
rem
rem

set termout off

drop table dept_p8;
create table dept_p8 (deptno number(2),dname char(20),loc char(20),last_updated date);
exit;

3.相关文件ppt_case8.ctl
LOAD DATA
INFILE *
continueif this(1:1) = '-' 
INTO TABLE DEPT_P8
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA 
-10,Sales,Virginia,
1/5/2000
-20,Sales,
Virginia,1/5/2000
-30,
Sales,Virginia,1/5/2000
-40,
Finance,Virginia,13/04/2001

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

四 详细步骤操作

1.以scott用户登录数据库运行ppt_case8.sql初始化环境
C:\sqlloader_exec\ppt_case8>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期一 8月 6 19:38:13 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> @ppt_case8
从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开

C:\sqlloader_exec\ppt_case8>

2.在cmd命令下执行sqlldr加载ppt_case8.ctl控制文件命令
C:\sqlloader_exec\ppt_case8>sqlldr userid=scott/tiger control=ppt_case8.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on 星期一 8月 6 19:38:48 2012

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

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

C:\sqlloader_exec\ppt_case8>

3.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on 星期一 8月 6 19:38:48 2012

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

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

加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行，最大 256000 字节
继续:    1: 1 =0X2d（字符'-'）,在当前的物理记录中
所用路径:       常规

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

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *    ,      CHARACTER           
DNAME                                NEXT     *    ,      CHARACTER           
    列的 SQL 串: "upper(:dname)"
LOC                                  NEXT     *    ,      CHARACTER           
    列的 SQL 串: "upper(:loc)"
LAST_UPDATED                         NEXT     *    ,      DATE dd/mm/yyyy     

记录 1: 被拒绝 - 表 DEPT_P8 的列 LAST_UPDATED 出现错误。
ORA-01858: 在要求输入数字处找到非数字字符


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


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

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

从星期一 8月  06 19:38:48 2012开始运行
在星期一 8月  06 19:38:49 2012处运行结束

经过时间为: 00: 00: 00.96
CPU 时间为: 00: 00: 00.03(可?

4.查看sqlldr bad日志信息
-10,Sales,Virginia,
1/5/2000

5.查看数据库加载成功的数据
C:\sqlloader_exec\ppt_case8>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期一 8月 6 19:43:33 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_p8;

    DEPTNO DNAME                LOC                  LAST_UPDAT
---------- -------------------- -------------------- ----------
        20 SALES                IRGINIA              01-5月 -00
        30 ALES                 VIRGINIA             01-5月 -00
        40  INANCE              VIRGINIA             13-4月 -01

SQL>

五 个人总结

从源flat数据与导入数据库的对比分析

源:
-10,Sales,Virginia,
1/5/2000
-20,Sales,
Virginia,1/5/2000
-30,
Sales,Virginia,1/5/2000
-40,
Finance,Virginia,13/04/2001
导入成功:
        20 SALES                IRGINIA              01-5月 -00
        30 ALES                 VIRGINIA             01-5月 -00
        40  INANCE              VIRGINIA             13-4月 -01

由于从"-"开始的下一行向前移会去掉第一个字符.而1/5/2000去掉第一个字符后只剩下了/5/2000 不符合日期格式,因此会将第一行数拒绝.
其他行也是同样的,非"-"符号开始的行第一个字符都被去掉然后导入数据库中.

对于此种情况,oracle提供了preserve参数. 虽然可以保留非'-'开始的行的首位字符,但同样的'-'也被一并保存下来.即如下:
控制文件内容:
LOAD DATA
INFILE *
continueif this preserve (1:1)   = x'2D' 
INTO TABLE DEPT_P8
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA 
-10,Sales,Virginia,
1/5/2000
-20,Sales,
Virginia,1/5/2000
-30,
Sales,Virginia,1/5/2000
-40,
Finance,Virginia,13/04/2001

导入进数据库中的数据:
C:\sqlloader_exec\ppt_case8>sqlldr userid=scott/tiger control=ppt_case8.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on 星期一 8月 6 20:54:32 2012

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

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

C:\sqlloader_exec\ppt_case8>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期一 8月 6 20:54:37 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_p8;

    DEPTNO DNAME                LOC                  LAST_UPDAT
---------- -------------------- -------------------- ----------
       -10 SALES                VIRGINIA             01-5月 -00
       -20 SALES                VIRGINIA             01-5月 -00
       -30 SALES                VIRGINIA             01-5月 -00
       -40  FINANCE             VIRGINIA             13-4月 -01

SQL>

六 资料参考引用

http://afy.itpub.net/post/1128/22073

Oracle9i Database Utilities
Release 2 (9.2)
Part Number A96652-01

http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch05.htm