Oracle 的 External Tables 被定義為可以讀取資料庫外部的資料，如：文字檔案、CSV...等檔案；您可以把它當成是一個可以不需事先將資料導入( load in ) 資料庫就可以進行 Query 的 View，您可以針對 external table 的 data 作 select、join、sort 等動作，也可以對 external tables 建立 views，synonymes。但是，你不能對這些 external 作DML 的動作(UPDATE, INSERT, or DELETE)，也就是只能讀取( Read Only )，而且也不能建立 index。
下面的範例中，將對不同格式 Comma delimited 及 Fixed field length 的檔案來作示範。
Step I: 建立 External Tables 所需的文字檔
Comma delimited (文字檔案、CSV) 檔案資料格式如下，各欄位以 ',' 作為區隔
200,Whalen,Administration,1987.09.17 00:00:00
201,Hartstein,Marketing,1996.02.17 00:00:00
202,Fay,Marketing,1997.08.17 00:00:00
114,Raphaely,Purchasing,1994.12.07 00:00:00
115,Khoo,Purchasing,1995.05.18 00:00:00
116,Baida,Purchasing,1997.12.24 00:00:00
117,Tobias,Purchasing,1997.07.24 00:00:00
118,Himuro,Purchasing,1998.11.15 00:00:00
Step II: 建立 Directory 物件
| CREATE OR REPLACE DIRECTORY ext_dir as 'C:\oraclexe\ExternalTable'; |
為了預防 ORA-29913 的錯誤，因此需授權需要讀取資料的人有讀／寫的權限
| GRANT read, write on directory ext_dir to polin; |
Step III: 建立 External Tables
CREATE TABLE ext_table_csv (
employee_id number(6),
last_name varchar2(25),
department_name varchar2(30),
hire_date date
)
ORGANIZATION EXTERNAL (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
(employee_id, last_name, department_name,hire_date CHAR(19) DATE_FORMAT DATE MASK "YYYY.MM.DD HH24:MI:SS")
)
location ('user_dept.csv')
)
reject limit unlimited; |
ACCESS PARAMETERS 參數指定，此 External Tables 的格式，以 NEWLINE為每筆資料的分隔，每個欄位用 ',' 分隔，若有無值的狀況，當成 NULL，四個欄位分別為 employee_id，last_name，department_name，hire_date，其中 hire_date 的 format 指定為 "YYYY.MM.DD HH24:MI:SS"
Step IV: Querying Data
| select * from ext_table_csv order by department_name; |
查詢出來的資料如下圖：
Fixed field length 檔案資料格式如下，各欄位的資料長度是固定的，以欄位的起始位址作為區隔，如前兩碼1-2是國家代碼，第3碼是區域碼
AR2
AU3
BE1
BR2
CA2
CH1
CN3
DE1
Fixed field length所需建立 External Tables 的語法如下：
CREATE TABLE ext_table_fixed (
country_id CHAR(2),
region_id NUMBER(1)
)
ORGANIZATION EXTERNAL (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields (
country_id position(1:2) ,
region_id position(3:4)
)
)
location ('country_region.txt')
)
reject limit unlimited;
延伸討論
若我們將 External Table 實體目錄 'C:\oraclexe\ExternalTable' 下的檔案更名或刪除，則系統會有錯誤訊息，因此可以得知，它是直接去讀取該目錄下的檔案內容。
Populating Tables using the INSERT command
如果您想對這些資料作進一步的操作，可以是使用"insert into ... select from" 的語法來將這些資料加入 Database 中，這種方式比 SQL*Loader的效率好很多。
Dropping External Tables
如果你不需要這些 External Tables，可以使用 DROP TABLE statement 將它們移除，不過這不會影響到存在於資料庫外的檔案。
Summary
基本上你也可以使用 SQL*Loader來完成以上的事情，但是相較之下 External Tables 彈性跟效率都比較好。External Table尚有其他的定義和使用注意事項( Ex. Performance )，在使用前最好是先評估一下。
參考文獻
External tables in Oracle http://www.adp-gmbh.ch/ora/misc/ext_table.html
PTC-Windchill (1)