ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 测试TOM=SQLLOADER加载制表符号数据

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

原创 Linux操作系统 作者:oracle_db 时间:2012-05-14 10:15:28 0 删除 编辑
用字表符分割的数据,有两种加载办法
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 测试TOM=SQLLOADER1
请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    306165