使用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。

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

相關文章