发表于: 2008.07.11 23:43
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/466335
---------------------------------------------------------------
前两天一个朋友问我这个问题，如何将文件中的空格作为列的值利用SQLLOADER加载到数据库中。
首先建立一张测试用表：
SQL> CREATE TABLE T_LOAD_SPACE (ID NUMBER, NAME VARCHAR2(30), AGE NUMBER);
表已创建。
为了简单，将数据放到控制文件中，控制文件和数据内容如下：
LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
Name CHAR TERMINATED BY ',',
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,A,15
2, B,25
3,C ,35
4, D ,45
5, ,55
可以看到，记录1的NAME列不包含空格，记录2的NAME列包含空格前缀，记录3的NAME列包括空格后缀，记录4的NAME列同时包含空格前缀和后缀，而记录5的NAME列只包含4个空格。
下面执行SQLLOADER操作导入数据：
E:>sqlldr yangtk/yangtk control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:29:20 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 5
检查导入的数据：
SQL> SELECT * FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 B 25
3 C 35
4 D 45
5 55
这样看不明显，只能看到空格前缀导入了，下面将空格转化一下：
SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
2 FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 spaceB 25
3 Cspace 35
4 spaceDspace 45
5 spacespacespacespace 55
可以看到，这种情况无论是前缀空格，还是后缀空格都是可以导入的。
下面稍微修改一下控制文件：
LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
NAME CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,A,15
2, B,25
3,C ,35
4, D ,45
5, ,55
添加一个常用的选项，OPTIONALLY ENCLOSED BY '"'，再次执行输入数据的载入：
E:>sqlldr yangtk/yangtk control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:35:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 5
检查表记录：
SQL> SELECT * FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 B 25
3 C 35
4 D 45
5 55
SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
2 FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 B 25
3 Cspace 35
4 Dspace 45
5 55
可以看到，前缀空格和纯空格列都没有导入，而后缀空格得以保留。
为了解决这种情况，可以使用PRESERVE BLANKS选项：
LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
NAME CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' PRESERVE BLANKS,
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,A,15
2, B,25
3,C ,35
4, D ,45
5, ,55
执行导入：
E:>sqlldr yangtk/yangtk control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:35:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 5
查看这次导入的结果：
SQL> SELECT * FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 B 25
3 C 35
4 D 45
5 55
SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
2 FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 spaceB 25
3 Cspace 35
4 spaceDspace 45
5 spacespacespacespace 55
可以看到，这次的结果和第一次完全一致，无论前缀空格还是后缀空格都成功导入。
另一种解决方法就是在数据中将这种数值全部用引号引起来，这样可以达到同样的目的：
LOAD DATA
INFILE *
TRUNCATE INTO TABLE T_LOAD_SPACE
(
ID CHAR TERMINATED BY ',',
NAME CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
AGE CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
1,"A",15
2," B",25
3,"C ",35
4," D ",45
5," ",55
执行导入操作：
E:>sqlldr yangtk/yangtk control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 00:35:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 5
检查表结果：
SQL> SELECT * FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 B 25
3 C 35
4 D 45
5 55
SQL> SELECT ID, REPLACE(NAME, ' ', 'space') NAME, AGE
2 FROM T_LOAD_SPACE;
ID NAME AGE
---------- ------------------------------ ----------
1 A 15
2 spaceB 25
3 Cspace 35
4 spaceDspace 45
5 spacespacespacespace 55
