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

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 MIS 的頭像
    MIS

    MISTECH 技術手抄本

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

    E-mail轉寄