sqlloader分隔符文件中有三个字段，是否将任意两个字段导入表中？

applezh提问：

分隔符文件 是否可以指定字段

比如：
分隔符文件中有三个字段，是否将任意两个字段导入表中？

[@more@]

这个问题主要是filler关键字的使用问题，以一个5字段导入3个和测试来回答：

1、在qiuyb用户下建一张表，只包含A,C,E三个字段

SQL> show user
USER is "QIUYB"
SQL> desc t1
Name Null? Type
------------ -------- ---------------
A VARCHAR2(10)
C VARCHAR2(10)
E VARCHAR2(10)

2、包含数据的文件1.txt则有7个字段
$ cat 1.txt
"a","b","c","d","e","f","g"
"aaa","bb","c","d0","eee","f","gg"
"baa","db","f","d1","eee","f","gg"
"caa","eb","g","d2","eee","f","gg"
"daa","fb","h","d3","eee","f","gg"

3、编写control文件，使用filler关键字,把不需要的字段filler掉

$ cat 1.ctl
LOAD DATA
INFILE '1.txt'
INTO TABLE T1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
a char(10),
b filler char(10),
c char(10),
d filler char(10),
e char(10),
f filler char(10),
g filler char(10)
)

4、执行sqlldr操作,可以见到成功导入5条记录

$ sqlldr qiuyb/qiuyb control=1.ctl log=1.log

SQL*Loader: Release 9.2.0.6.0 - Production on Sat Sep 9 12:23:29 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 5

5、到库内查询，校验一下。

$ sqlplus qiuyb/qiuyb

SQL*Plus: Release 9.2.0.6.0 - Production on Sat Sep 9 12:24:08 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> select * from t1;

A C E
---------- ---------- ----------
a c e
aaa c eee
baa f eee
caa g eee
daa h eee

SQL>

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

转载于:http://blog.itpub.net/717880/viewspace-864127/