测试TOM=SQLLOADER加载制表符号数据

用字表符分割的数据，有两种加载办法

1.TERMINATED BY  X '09'

2.TERMINATED BY WHITESPACE

=============以WHITESPACE来测试制表符数据的加载=================

会话1：清空测试表数据，创建新的控制文件加载新的带有制表符号的数据。

SQL> select * from dept_load;

    DEPTNO DNAME          LOC

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

        10 sales          abc,usa

        20 aaa            xx,"yy"

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

SQL> truncate table dept_load;

Table truncated.

SQL> 

会话1：建立新的控制文件如下

[oracle@oraclelinux ~]$ ls -ltr dept*

-rw-r--r--  1 oracle oinstall  202 May 11 16:02 dept_load2.ctl

-rw-r--r--  1 oracle oinstall 1679 May 11 16:03 dept_load2.log

-rw-r--r--  1 oracle oinstall    1 May 11 16:03 dept_load2.bad

[oracle@oraclelinux ~]$ rm -rf dept*

[oracle@oraclelinux ~]$ ls -ltr dept*

ls: dept*: No such file or directory

[oracle@oraclelinux ~]$ vi dept_load3.ctl

load data

infile *

into table dept_load

replace

fields terminated by whitespace

(deptno,dname,loc)

begindata

10              sales           virginia

~

~

~

会话1：加载新数据

[oracle@oraclelinux ~]$  sqlldr userid=scott/scott control=dept_load3.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 10:34:02 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 1

[oracle@oraclelinux ~]$ ls -ltr dept8

ls: dept8: No such file or directory

[oracle@oraclelinux ~]$ ls -ltr dept*

-rw-r--r--  1 oracle oinstall  129 May 14 10:32 dept_load3.ctl

-rw-r--r--  1 oracle oinstall 1548 May 14 10:34 dept_load3.log

[oracle@oraclelinux ~]$ exit

exit

SQL> select * from dept_load;

    DEPTNO DNAME          LOC

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

        10 sales          virginia

SQL> !

[oracle@oraclelinux ~]$ more dept_load3.log

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 10:34:02 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   dept_load3.ctl

Data File:      dept_load3.ctl

  Bad File:     dept_load3.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:    none specified

Path used:      Conventional

Table DEPT_LOAD, loaded from every logical record.

Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype

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

DEPTNO                              FIRST     *  WHT      CHARACTER            

DNAME                                NEXT     *  WHT      CHARACTER            

LOC                                  NEXT     *  WHT      CHARACTER            

Table DEPT_LOAD:

  1 Row 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:                  49536 bytes(64 rows)

Read   buffer bytes: 1048576

Total logical records skipped:          0

Total logical records read:             1

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Mon May 14 10:34:02 2012

Run ended on Mon May 14 10:34:02 2012

Elapsed time was:     00:00:00.59

CPU time was:         00:00:00.07

[oracle@oraclelinux ~]$ 

---

=====================使用16进制格式的制表符号====================

会话1：清空测试表DEPT_LOAD,创建新控制文件

[oracle@oraclelinux ~]$ ls -ltr dept*

-rw-r--r--  1 oracle oinstall  129 May 14 10:32 dept_load3.ctl

-rw-r--r--  1 oracle oinstall 1548 May 14 10:34 dept_load3.log

[oracle@oraclelinux ~]$ cp dept_load3.ctl dept_load4.ctl

[oracle@oraclelinux ~]$ rm -rf dept_load3.*

[oracle@oraclelinux ~]$ vi dept_load4.ctl

      1 load data

      2 infile *

      3 into table dept_load

      4 replace

      5 fields terminated by X'09'====16进制的制表符号表示方法

      6 (deptno,dname,loc)

      7 begindata

      8 10              sales           virginia

~

[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load4.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 10:42:54 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 1

[oracle@oraclelinux ~]$ exit

exit

SQL> select * from dept_load;

    DEPTNO DNAME          LOC

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

        10                sales

SQL> 

测试结果发现在此，一旦SQLLDR 遇到一个制表符，就会输出一个值。因此，将10 赋给DEPTNO，DNAME 得到了NULL，

因为在第一个制表符和制表符的下一次出现之间没有数据。Sales 赋给了LOC。

这个时候如果我们把数据中的制表符号由两个变成一个，那么DNAME就会正常显示

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

转载于:http://blog.itpub.net/15720542/viewspace-723490/