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;

查詢出來的資料如下圖:
externalTableQuery.png  

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' 下的檔案更名或刪除,則系統會有錯誤訊息,因此可以得知,它是直接去讀取該目錄下的檔案內容。

externalTableQueryError.png  

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

創作者介紹
MIS

MISTECH 技術手抄本

MIS 發表在 痞客邦 PIXNET 留言(0) 人氣()