使用SQL*Loader创建外部表：

---创建目录对象并授权给Scott用户：
SQL> create or replace directory loader_home as '/home/oracle/dirhome';
Directory created.
--授权：
SQL> grant read,write on directory loader_home to scott;
Grant succeeded.

---在scott用户下创建外部表：
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"

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

--查看表结构：
SQL> desc myloader
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
DNAME VARCHAR2(12)
LNAME VARCHAR2(10)

---在操作系统层创建两个存放外部数据的文件：
[oracle@enmo ~]$ cd dirhome/
[oracle@enmo dirhome]$ pwd
/home/oracle/dirhome
[oracle@enmo dirhome]$

--创建外部文件1：
[oracle@enmo dirhome]$ vi dt1.csv
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
~
--创建外部文件2：
[oracle@enmo dirhome]$ vi dt2.csv
40,OPERATIONS1,BOSTON0
50,OPERATIONS2,BOSTON9
60,OPERATIONS3,BOSTON8
70,OPERATIONS4444,BOSTON7
~

--查看创建的两个外部文件：
[oracle@enmo dirhome]$ ls
dt1.csv dt2.csv
[oracle@enmo dirhome]$ ll
total 8
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall 95 Nov 1 22:39 dt2.csv
[oracle@enmo dirhome]$

--创建外部表控制文件：
[oracle@enmo dirhome]$
[oracle@enmo dirhome]$ vi load.ctl
load data
infile 'dt1.csv'
infile 'dt2.csv'
badfile 'load.bad' #指明坏文件
into table myloader
truncate
fields terminated by ','
(id,dname,lname)
~

--查看控制文件：
[oracle@enmo dirhome]$ ls
dt1.csv dt2.csv load.ctl

---导入外部文件到Scott用户下的myloader表：
[oracle@enmo dirhome]$ sqlldr scott/tiger control=load.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Nov 1 22:54:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 8
[oracle@enmo dirhome]$

--查看本目录下的文件：
[oracle@enmo dirhome]$
[oracle@enmo dirhome]$ ls
dt1.csv dt2.csv load.bad load.ctl load.log
#多生成了一个load.bad的坏文件，是存放因为不符合导入条件的数据。

--到Scott用户查看表myloader的数据：
SQL> select * from myloader;
ID DNAME LNAME
---------- ------------ ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
40 OPERATIONS1 BOSTON0
50 OPERATIONS2 BOSTON9
60 OPERATIONS3 BOSTON8
7 rows selected.
#通过Scott用户下的SQL查询，发现外部文件dt2.csv中的ID号为70的数据没有被导入表myloader中。

--尝试读取load.bad文件的额数据：
[oracle@enmo dirhome]$
[oracle@enmo dirhome]$ cat load.bad
70,OPERATIONS4444,BOSTON7
#看到因为第二个字段的长度超出了设置的长度，所以没有被导入到表中。

---空值null的处理：
--在Scott用户另外创建一个表hisloader：
SQL> create table hisloader(
2 id number(3),
3 dname varchar2(12),
4 lname varchar2(10));
Table created.

--编辑外部文件dt3.csv以及控制文件：
[oracle@enmo dirhome]$ vi dt3.csv
101,OPERATIONS5,BOSTON1
102,OPERATIONS6,BOSTON2
103,OPERATIONS7
~
#共3条数.

--创建控制文件：
[oracle@enmo dirhome]$ vi load1.ctl
load data
infile 'dt3.csv'
badfile 'load.bad'
into table hisloader
truncate
fields terminated by ','
trailing nullcols
(id,dname,lname)
~

--利用sqlldr将外部数据导入表hisloader中：
[oracle@enmo dirhome]$ sqlldr scott/tiger control=load1.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Nov 1 23:22:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 3
[oracle@enmo dirhome]$
#可到有3条数据导入表中。

--到Scott用户查看hisloader表的数据：
SQL> select * from hisloader;
ID DNAME LNAME
---------- ------------ ----------
101 OPERATIONS5 BOSTON1
102 OPERATIONS6 BOSTON2
103 OPERATIONS7
#可以看到含有空值的数据也可以被导入到表hisloader中。

--查看目录下的外部文件：
[oracle@enmo dirhome]$ ll
total 32
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall 95 Nov 1 22:39 dt2.csv
-rw-r--r-- 1 oracle oinstall 64 Nov 1 23:18 dt3.csv
-rw-r--r-- 1 oracle oinstall 136 Nov 1 23:21 load1.ctl
-rw-r--r-- 1 oracle oinstall 1588 Nov 1 23:22 load1.log
-rw-r--r-- 1 oracle oinstall 26 Nov 1 22:54 load.bad
-rw-r--r-- 1 oracle oinstall 134 Nov 1 22:50 load.ctl
-rw-r--r-- 1 oracle oinstall 1852 Nov 1 22:54 load.log
[oracle@enmo dirhome]$

对于含有控制的数据，就需要在控制文件中添加语句trailing nullcols。

ITPub博客

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

最新文章