Oracle 將大量查詢結果匯出至文字檔的解決方案：外部表實作指南

在資料庫開發與維護過程中，常遇到需要將龐大查詢結果匯出至文字檔的需求。有位開發者嘗試直接在 SQL 語句中引用本地檔案路徑（如 C:/Users/aj.moon/Desktop/TEMP/A5A_DOCNUM.TXT），卻遭遇「Syntax error near 'C'」錯誤。這是因為 Oracle 無法直接在 SQL 中讀取外部文字檔，必須先將資料載入資料庫內部，或透過外部表機制讓資料庫能夠查詢外部平面檔案。

為何無法直接引用文字檔？

Oracle 的 SQL 引擎只能操作資料庫內部的物件（表、視圖、同義詞等）。外部檔案不在資料字典中，無法被直接查詢。嘗試在 IN 子句中寫入檔案路徑屬於語法錯誤，系統無法解析。

解決方案：建立外部表

外部表允許 Oracle 以唯讀方式查詢儲存在資料庫外部的平面檔案（CSV、定寬格式等）。步驟如下：

1. 在資料庫伺服器上建立目錄物件，指向存放文字檔的作業系統目錄。
2. 使用 CREATE TABLE ... ORGANIZATION EXTERNAL 定義外部表結構，對應檔案欄位。
3. 即可像查詢一般表一樣 SELECT * FROM ext_doc_no，並可加入 JOIN、WHERE 等條件。

範例：將 4000 筆 DOC_NO 匯入外部表

-- 1. 建立目錄物件（需 DBA 權限）
CREATE OR REPLACE DIRECTORY ext_data_dir AS '/u01/app/oracle/ext_data';

-- 2. 建立外部表
CREATE TABLE ext_doc_no (
  doc_no VARCHAR2(20)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' 
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('A5A_DOCNUM.TXT')
)
REJECT LIMIT UNLIMITED;

-- 3. 查詢並套用至主查詢
SELECT plan_id, el_id, niin, item_name, project_cd,
       CASE WHEN lcmc_doc_no IS NULL THEN mrodoc ELSE lcmc_doc_no END AS "DOCUMENT NUMBER"
FROM   eqlist_view
WHERE  plan_id IN (736, 1011)
AND    lcmc_doc_no IN (SELECT doc_no FROM ext_doc_no)
ORDER BY niin, "DOCUMENT NUMBER";

效能考量與替代方案

• 外部表無法建立索引，若頻繁查詢大量資料，建議先用 INSERT INTO real_table SELECT * FROM ext_doc_no 載入實體表，再建立索引。
• 一次性匯出可用 SQL*Loader 或 UTL_FILE 寫入檔案；若需定期同步，外部表維護成本最低。
• Oracle 18c 起支援 ORACLE_BIGDATA、ORACLE_HIVE 等更多外部表類型，適用於雲端/大數據場景。

常見錯誤排查

• ORA-29913：目錄路徑不存在或權限不足，檢查 OS 層面 oracle 使用者讀取權限。
• KUP-04040：檔案格式不符（分隔符、換行符），調整 ACCESS PARAMETERS。
• 字元集不匹配：外部表預設使用資料庫字元集，若檔案為 UTF-8 而資料庫為 AL32UTF8 以外，需在 ACCESS PARAMETERS 指定 CHARACTERSET AL32UTF8。

掌握外部表技巧，即可輕鬆解決「大量清單匯入查詢」的經典難題，避免手動拼接超長 IN 清單導致的 SQL 解析效能問題與長度限制。