兩大匯出路徑對比
| 方案 | 優點 | 缺點 | 適用場景 |
|---|
| SQL*Plus SPOOL / MARKUP CSV | 零程式碼、客戶端執行、支援遠端 | 受限於客戶端記憶體/網路、格式控制弱 | 臨時匯出、小資料量、DBA 手工操作 |
| PL/SQL UTL_FILE | 伺服器端直寫、高效能、完整格式控制、可排程 | 需目錄物件權限、僅能寫伺服器本地/共享儲存 | 定期 ETL、大資料量、固定格式交換檔 |
UTL_FILE 核心步驟
- 建立 Directory Object (10g+ 推薦) 或設定
UTL_FILE_DIR (舊版、不安全、已棄用)。
UTL_FILE.FOPEN 開啟檔案 (模式 'W' 覆寫 / 'A' 附加)。- 游標迴圈
FETCH → UTL_FILE.PUT_LINE / PUTF 寫入。
UTL_FILE.FCLOSE / FCLOSE_ALL 關閉並刷新緩衝。
現代寫法：Directory Object + 權限控管
-- 1. 建立目錄物件 (需 CREATE ANY DIRECTORY 權限)
CREATE OR REPLACE DIRECTORY export_dir AS '/u01/app/oracle/export';
-- 2. 授權給應用使用者
GRANT READ, WRITE ON DIRECTORY export_dir TO app_user;
-- 3. PL/SQL 匯出範例
DECLARE
fh UTL_FILE.FILE_TYPE;
CURSOR c_data IS SELECT id, name, amt FROM sales WHERE dt = TRUNC(SYSDATE);
BEGIN
fh := UTL_FILE.FOPEN('EXPORT_DIR', 'sales_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.csv', 'W', 32767);
UTL_FILE.PUT_LINE(fh, 'ID,NAME,AMT'); -- 標頭
FOR r IN c_data LOOP
UTL_FILE.PUT_LINE(fh, r.id||','||r.name||','||r.amt);
END LOOP;
UTL_FILE.FCLOSE(fh);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(fh) THEN UTL_FILE.FCLOSE(fh); END IF;
RAISE;
END;
/
大欄位處理：超過 32767 位元組限制
PUT_LINE 單行上限 32767 字元 (含換行)。- CLOB/長字串需分塊寫入：
UTL_FILE.PUT_RAW + UTL_RAW.CAST_TO_RAW 或迴圈 SUBSTR 切片。
效能優化技巧
- 批次緩衝：累積 N 列 (如 1000) 再呼叫
PUT_LINE，或使用 UTL_FILE.FFLUSH 定期刷盤。
- 並行匯出：
DBMS_PARALLEL_EXECUTE 將查詢切片，多 Session 同時寫入不同檔案，最後合併。
- 壓縮：匯出後呼叫 OS 指令
gzip 或 PL/SQL UTL_COMPRESS 壓縮。
- 編碼：
UTL_FILE.FOPEN(..., 'W', 32767, 'AL32UTF8') 指定字元集，避免中文亂碼。
常見錯誤與排查
| 錯誤 | 原因 | 解決 |
|---|
| ORA-29280: invalid directory path | 目錄物件路徑不存在或權限不足 | OS 層建立目錄、chown oracle:oinstall、chmod 750 |
| ORA-29283: invalid file operation | 檔案被佔用/磁碟滿/超過配額 | 檢查磁碟空間、lsof 釋放佔用 |
| ORA-06512: at "SYS.UTL_FILE" | 未捕獲例外導致檔案未關閉 | 加入 EXCEPTION 區塊確保 FCLOSE |
總結
UTL_FILE 是伺服器端資料交換的標準工具，配合 Directory Object 權限模型、批次緩衝、並行切片，可穩定支撐 GB 級文字檔匯出需求。
