您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle UTL_FILE 寫入檔案實戰：存儲過程實現字串分塊寫入

編輯：Oracle 資料庫基礎

PL/SQL 檔案 I/O：UTL_FILE 套件完整應用指南

Oracle 提供 UTL_FILE 套件讓 PL/SQL 直接讀寫作業系統檔案，適用於報表匯出、日誌記錄、資料交換等場景。本文提供完整的分塊寫入存儲過程範例，解決 VARCHAR2 32KB 限制與大量資料寫入效能問題。

前置配置

資料庫參數 (需重啟)：

*.utl_file_dir = '/u01/app/oracle/export'
-- 12c+ 建議改用 Directory 物件，不推薦此參數

建立 Directory 物件 (10g+ 推薦)：

CREATE OR REPLACE DIRECTORY EXPORT_DIR AS '/u01/app/oracle/export';
GRANT READ, WRITE ON DIRECTORY EXPORT_DIR TO app_user;

通用分塊寫入存儲過程

CREATE OR REPLACE PROCEDURE sp_write_to_file (
  p_directory  IN VARCHAR2,  -- Directory 物件名
  p_filename   IN VARCHAR2,
  p_content    IN CLOB       -- 支援超大字串
) IS
  v_file       UTL_FILE.FILE_TYPE;
  v_buffer     VARCHAR2(32767);
  v_amount     CONSTANT PLS_INTEGER := 32767;
  v_offset     PLS_INTEGER := 1;
  v_length     PLS_INTEGER;
BEGIN
  -- 開啟檔案 (wb = 二進位寫入/覆蓋)
  v_file := UTL_FILE.FOPEN(p_directory, p_filename, 'wb', 32767);
  
  v_length := DBMS_LOB.GETLENGTH(p_content);
  
  WHILE v_offset < v_length LOOP
    -- 讀取 CLOB 區塊
    DBMS_LOB.READ(p_content, v_amount, v_offset, v_buffer);
    -- 寫入檔案 (TRUE = 自動 flush)
    UTL_FILE.PUT_RAW(v_file, UTL_RAW.CAST_TO_RAW(v_buffer), TRUE);
    v_offset := v_offset + v_amount;
  END LOOP;
  
  UTL_FILE.FCLOSE(v_file);
  
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(v_file) THEN
      UTL_FILE.FCLOSE(v_file);
    END IF;
    RAISE;  -- 重拋例外供上層處理
END sp_write_to_file;

關鍵技術點解析

技術點	說明
CLOB 參數	突破 VARCHAR2 32KB 限制，支援 GB 級內容
DBMS_LOB.READ	分塊讀取 CLOB，避免記憶體暴增
UTL_RAW.CAST_TO_RAW	VARCHAR2 → RAW 轉換，PUT_RAW 需求
FOPEN max_linesize=32767	單次寫入緩衝區上限，提升 I/O 效率
PUT_RAW 第三參數 TRUE	自動 FLUSH，確保資料落盤，避免緩衝區遺失
例外區 FCLOSE	資源釋放保護，防止檔案鎖定洩漏

呼叫範例

DECLARE
  v_sql  CLOB;
BEGIN
  -- 動態生成大量 SQL/報表內容
  SELECT XMLSERIALIZE(DOCUMENT XMLELEMENT("DATA", XMLAGG(XMLELEMENT("ROW", XMLFOREST(*) )))
         INTO v_sql FROM large_table;
  
  sp_write_to_file('EXPORT_DIR', 'large_export.xml', v_sql);
  DBMS_OUTPUT.PUT_LINE('匯出完成');
END;

常見錯誤與排查

錯誤代碼	原因	解決
ORA-29280: invalid directory path	Directory 物件路徑不存在/權限不足	OS 層建立目錄、chown oracle:oinstall、GRANT READ/WRITE
ORA-29283: invalid file operation	檔案名無效、磁碟滿、超過 OS 檔案大小限制	檢查磁碟空間、ulimit -f、檔案系統大檔支援
ORA-29282: invalid file ID	檔案控制代碼失效 (已關閉/未開啟)	確認 FOPEN 成功、例外處理正確 FCLOSE