sqlldr加载固定格式数据（POSITION DATA）

来自http://blog.sina.com.cn/s/blog_a7e2684901012582.html

sqlldr加载固定格式数据（POSITIONDATA）

一、实验环境：

系统环境：window2007

数据库环境：

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production

PL/SQL Release 9.0.1.1.1 - Production

CORE    9.0.1.1.1       Production

TNS for 32-bit Windows: Version 9.0.1.1.0 - Production

NLSRTL Version 9.0.1.1.1 - Production验环境

二、原理：

1）加载的数据，给定宽，但是定宽文件比丁杰文件格式可能大的多。Position的使用。

2）*的使用:*指示控制文件得出上一个字段在哪里结束的。

3）绝对偏移量和相对偏移量：POSITION子句中的结束位置必须是数据结束的绝对列位置。

 

三、实验过程：

1）加载的数据，给定宽，但是定宽文件比丁杰文件格式可能大的多。Position的使用。

用户：scott/tiger

建表：dept_03

SQL>  create table dept_04 

(deptno   number(30)  primary key,

daname  varchar2(30),

loc      varchar2(30));

控制文件：dept_04.ctl

LOAD DATA

INFILE *

INTO TABLE DEPT_04 

REPLACE

(DEPTNO position(1:2),

DANAME position(3:16),

LOC  position(17:29)

)

BEGINDATA

10Accounting     Virginia,USA

Position （x:y）从x-y的数据写入相应的行，但是x-y的数据类型要符合表的要求。

不符合要求会直接告诉，数据结构不匹配

导入数据：

C:\oracle\control>sqlldr userid=scott/tiger control=dept_04.ctl

 

SQL*Loader: Release 9.0.1.1.1 - Production on 星期五 8月 10 11:05:55 2012

 

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

 

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

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

查看表：

SQL> select * from dept_04;

 

    DEPTNO DANAME                         LOC

---------- ------------------------------ ------------------------------

        10 Accounting                      Virginia,USA

查看日志：

 

表DEPT_03: 

1 行加载成功

由于数据错误, 0 行没有加载。

由于所有 WHEN 子句失败, 0 行没有加载。

由于所有字段都为空的, 0 行没有加载。

 

 

为结合数组分配的空间:     2304字节（64行）

读取   缓冲区字节数: 1048576

 

跳过的逻辑记录总数:        0

读取的逻辑记录总数:        1

拒绝的逻辑记录总数:        0

废弃的逻辑记录总数:        0

2）重叠方法的使用

给表增加新的一列:

SQL> alter table dept_04 add endier_line varchar2(30);

SQL> desc dept_04;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- -------------------------

 

 DEPTNO                                    NOT NULL NUMBER(30)

 DANAME                                             VARCHAR2(30)

 LOC                                                VARCHAR2(30)

 ENDIER_LINE                                        VARCHAR2(30)

给控制文件新增一列：

LOAD DATA

INFILE *

INTO TABLE DEPT_04 

REPLACE

(DEPTNO position(1:2),

DANAME position(3:16),

LOC  position(17:29),

ENDIER_LINE position(1:29)

)

BEGINDATA

10Accounting     Virginia,USA

使用POSITION时，可以使用相对偏移量，也可以使用绝对偏移量。在前面的例子中使用了绝对偏移量，我们明确的指示了字段从哪里开始，到哪里结束。

 

3）*的使用

控制文件：

 

LOAD DATA

INFILE *

INTO TABLE DEPT_04 

REPLACE

(DEPTNO position(1:2),

DANAME position(*:16),

LOC  position(*:29),

ENDIER_LINE position(1:29)

)

BEGINDATA

10Accounting     Virginia,USA

导入数据：

C:\oracle\control>sqlldr userid=scott/tiger control=dept_04.ctl

 

SQL*Loader: Release 9.0.1.1.1 - Production on 星期五 8月 10 13:55:57 2012

 

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

 

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

查看表结构：

SQL> select * from dept_04;

 

    DEPTNO DANAME                         LOC

---------- ------------------------------ ------------------------------

ENDIER_LINE

------------------------------

        10 Accounting                      Virginia,USA

10Accounting     Virginia,USA

查看log:

表DEPT_04

已加载从每个逻辑记录

插入选项对此表REPLACE生效

 

   列名                        位置      长度  中止 包装数据类型

------------------------------ ---------- ----- ---- ---- ---------------------

DEPTNO                               1：2     2           CHARACTER            

DANAME                           NEXT：16    16           CHARACTER            

LOC                              NEXT：29    29           CHARACTER            

ENDIER_LINE                         1：29    29           CHARACTER            

 

记录 2: 被废弃 - 所有的列均为空的。

记录 3: 被废弃 - 所有的列均为空的。

 

表DEPT_04: 

1 行加载成功

由于数据错误, 0 行没有加载。

由于所有 WHEN 子句失败, 0 行没有加载。

由于所有字段都为空的, 2 行没有加载。

在此情况下，（*：16）与（3：16）是一样的。还可以相加（*+2:16）与（5：16）也是一样的。

4）绝对偏移量和相对偏移量：字段如果是连续的，还可以给定字段的长度，告诉开始位置即可。

控制文件：

LOAD DATA

INFILE *

INTO TABLE DEPT_04 

REPLACE

(DEPTNO position(1) char(2),

DANAME position(*) char(14),

LOC  position(*) char(13),

ENDIER_LINE position(1) char(29)

)

BEGINDATA

10Accounting     Virginia,USA

 

导入数据：

C:\oracle\control>sqlldr userid=scott/tiger control=dept_04.ctl

 

SQL*Loader: Release 9.0.1.1.1 - Production on 星期五 8月 10 13:55:57 2012

 

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

 

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

 

C:\oracle\control>write dept_04.log

 

C:\oracle\control>sqlldr userid=scott/tiger control=dept_04.ctl

 

SQL*Loader: Release 9.0.1.1.1 - Production on 星期五 8月 10 14:47:53 2012

 

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

 

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

查看表：

SQL> select * from dept_04;

 

    DEPTNO DANAME                         LOC

---------- ------------------------------ ------------------------------

ENDIER_LINE

------------------------------

        10 Accounting                      Virginia,USA

10Accounting     Virginia,USA

查看LOG

表DEPT_04

已加载从每个逻辑记录

插入选项对此表REPLACE生效

 

   列名                        位置      长度  中止 包装数据类型

------------------------------ ---------- ----- ---- ---- ---------------------

DEPTNO                                  1     2           CHARACTER            

DANAME                               NEXT    14           CHARACTER            

LOC                                  NEXT    13           CHARACTER            

ENDIER_LINE                             1    29           CHARACTER            

 

记录 2: 被废弃 - 所有的列均为空的。

记录 3: 被废弃 - 所有的列均为空的。

 

表DEPT_04: 

1 行加载成功

由于数据错误, 0 行没有加载。

由于所有 WHEN 子句失败, 0 行没有加载。

由于所有字段都为空的, 2 行没有加载。