用字表符分割的数据,有两种加载办法
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/