Oracle UTL_FILE 套件高效匯出查詢結果至文字檔函數範例

函數規格

輸入：查詢 SQL、分隔符、目錄名、檔名前綴。
輸出：寫入筆數（≥0）或錯誤碼（負數）。
功能：分頁讀取游標，批次寫入檔案，支援大量資料。

完整函數碼

CREATE OR REPLACE FUNCTION f_export_txt (
  i_query      IN VARCHAR2,
  i_separator  IN VARCHAR2 DEFAULT ',',
  i_dir        IN VARCHAR2,
  i_filename   IN VARCHAR2
) RETURN NUMBER IS
  v_file       UTL_FILE.FILE_TYPE;
  v_cursor     INTEGER := DBMS_SQL.OPEN_CURSOR;
  v_col_val    VARCHAR2(2000);
  v_col_cnt    NUMBER := 0;
  v_sep        VARCHAR2(10) := i_separator;
  v_cnt        NUMBER := 0;
  v_filename   VARCHAR2(100);
  v_status     INTEGER;
  v_batch_size CONSTANT NUMBER := 10000;
  v_total_rows NUMBER;
  v_loops      NUMBER;
  v_sql        VARCHAR2(2000);
BEGIN
  -- 組檔名（可加時間戳）
  v_filename := i_filename;

  -- 統計總筆數
  v_sql := 'SELECT COUNT(*) FROM (' || i_query || ')';
  EXECUTE IMMEDIATE v_sql INTO v_total_rows;
  v_loops := TRUNC(v_total_rows / v_batch_size);
  IF MOD(v_total_rows, v_batch_size) > 0 THEN v_loops := v_loops + 1; END IF;

  -- 開啟檔案
  v_file := UTL_FILE.FOPEN(i_dir, v_filename, 'W');

  -- 分批處理
  FOR i IN 1..v_loops LOOP
    v_sql := 'SELECT * FROM (SELECT m.*, ROWNUM r FROM (' || i_query || ') m WHERE ROWNUM <= ' || i * v_batch_size || ') WHERE r > ' || (i-1) * v_batch_size;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    v_col_cnt := DBMS_SQL.EXECUTE(v_cursor);

    -- 取得欄位定義
    FOR c IN 1..v_col_cnt LOOP
      DBMS_SQL.DEFINE_COLUMN(v_cursor, c, v_col_val, 2000);
    END LOOP;

    -- 迴圈寫入
    WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0 LOOP
      v_cnt := v_cnt + 1;
      FOR c IN 1..v_col_cnt LOOP
        DBMS_SQL.COLUMN_VALUE(v_cursor, c, v_col_val);
        UTL_FILE.PUT(v_file, v_col_val);
        IF c < v_col_cnt THEN UTL_FILE.PUT(v_file, v_sep); END IF;
      END LOOP;
      UTL_FILE.NEW_LINE(v_file);
    END LOOP;
  END LOOP;

  UTL_FILE.FCLOSE(v_file);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
  RETURN v_cnt;
EXCEPTION WHEN OTHERS THEN
  IF UTL_FILE.IS_OPEN(v_file) THEN UTL_FILE.FCLOSE(v_file); END IF;
  IF DBMS_SQL.IS_OPEN(v_cursor) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor); END IF;
  RETURN -SQLCODE;
END;
/

呼叫範例

DECLARE
  v_cnt NUMBER;
BEGIN
  v_cnt := f_export_txt(
    i_query     => 'SELECT empno, ename, sal FROM emp WHERE deptno=10',
    i_separator => '|',
    i_dir       => 'MY_DIR',
    i_filename  => 'emp_dept10.txt'
  );
  DBMS_OUTPUT.PUT_LINE('Exported rows: ' || v_cnt);
END;
/

設計亮點

• DBMS_SQL 動態游標：支援任意 SELECT 語句，無需硬編碼欄位。
• 分批讀取：避免大結果集佔用 PGA，v_batch_size 可調。
• 資源釋放：例外區確保檔案控制代碼與游標關閉。
• 回傳值語意：正數為筆數，負數為 Oracle 錯誤碼，方便呼叫端判斷。

結語

此函數可作為通用匯出工具，整合至排程或應用程式，支援 CSV、TSV、自訂分隔符，滿足資料交換、報表產出等需求。