这里讲述另外一种方法通过SQL*loader创建外部表，把外部数据直接写在控制文件上：

---查看hisloader表的数据：
SQL> select * from hisloader;
ID DNAME LNAME
---------- ------------ ----------
101 OPERATIONS5 BOSTON1
102 OPERATIONS6 BOSTON2
103 OPERATIONS7

---编辑外部表控制文件：
[oracle@enmo dirhome]$ vi load1.ctl
load data
infile*
badfile 'load.bad'
into table hisloader
truncate
fields terminated by ','
trailing nullcols
(id,dname,lname)
begindata
104,OPERATIONS5,BOSTON1
105,OPERATIONS6,BOSTON2
106,OPERATIONS7,"BOSTON3"
~

--导入数据：
[oracle@enmo dirhome]$ sqlldr scott/tiger control=load1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 2 11:26:27 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
[oracle@enmo dirhome]$

--查看表数据：
SQL> select * from hisloader;
ID DNAME LNAME
---------- ------------ ----------
104 OPERATIONS5 BOSTON1
105 OPERATIONS6 BOSTON2
106 OPERATIONS7 "BOSTON3"
#发现数据被替换了。

---创建另外一个外部表：
SQL> create table yourloader(
2 id number(3),
3 dname varchar2(12),
4 lname varchar2(10));
Table created.

---编辑控制文件：
[oracle@enmo dirhome]$ vi load2.ctl
load data
infile 'dt4.scv'
badfile 'base.bad'
into table yourloader
truncate
trailing nullcols
(
id TERMINATED BY ',',
dname TERMINATED BY ',',
lname TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'
)
~

--导入数据：
[oracle@enmo dirhome]$ sqlldr scott/tiger control=load2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 2 12:27:59 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7
[oracle@enmo dirhome]$
#导入了7条数据。

--查看外部表的数据：
SQL> select * from yourloader;
ID DNAME LNAME
---------- ------------ ----------
104 OPERATIONS5 BOSTON1
105 OPERATIONS6 BOSTON2
106 OPERATIONS7 BOS,TON3
101 OPERATIONS5 BOSTON1
102 OPERATIONS6 BOSTON2
103 OPERATIONS7
6 rows selected.
#实际就只有6条数据。

--使用以下语句：
trailing nullcols
(
id TERMINATED BY ',',
dname TERMINATED BY ',',
lname TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'
)
既能处理空值的数据，也解决了双引号’"’的问题。

ITPub博客

使用SQL*Loader创建外部表之二

最新文章