Oracle PL/SQL 除錯輸出三大法：DBMS_OUTPUT、暫存表、UTL_FILE 完整實戰指南

PL/SQL 開發中，「看不見變數值」是最常見的痛點。Oracle 提供三種層級的輸出機制，分別適用於不同資料量與場景。本文系統整理三大法、程式碼範本、限制條件與最佳實踐。

方法一：DBMS_OUTPUT — 輕量即時輸出

基本用法

SET SERVEROUTPUT ON SIZE UNLIMITED;  -- 客戶端開啟緩衝區 (SQL*Plus/SQL Developer/TOAD)

DECLARE
  v_msg VARCHAR2(32767) := 'Debug: 處理第 ' || v_cnt || ' 筆記錄';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

關鍵限制與演進

• 9i/10g 前：單次 PUT_LINE 限 255 字元，總緩衝區預設 20,000 字元 (可用 DBMS_OUTPUT.ENABLE(1000000) 調大)。
• 10g R2 起：單行限制放寬至 32,767 字元，總緩衝區可設 UNLIMITED。
• 12c 起：支援 DBMS_OUTPUT.PUT (不換行) + NEW_LINE 組合，彈性更高。

適用場景

開發階段少量變數追蹤、簡單邏輯驗證、迴圈關鍵點印輸。不適用：大量資料、生產環境高併發 (共享記憶體、非交易安全)。

方法二：暫存表 — 結構化大量輸出

建立表

CREATE GLOBAL TEMPORARY TABLE debug_log (
  seq_no   NUMBER GENERATED ALWAYS AS IDENTITY,
  log_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  module   VARCHAR2(100),
  message  VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

優勢：Session 隔離、自動清理、支援 SQL 查詢/過濾/匯出、無 32K 長度限制 (可用 CLOB)。

寫入封裝範例

CREATE OR REPLACE PACKAGE debug_pkg AS
  PROCEDURE log(p_module VARCHAR2, p_msg VARCHAR2);
  PROCEDURE log_clob(p_module VARCHAR2, p_msg CLOB);
END;
/

CREATE OR REPLACE PACKAGE BODY debug_pkg AS
  PROCEDURE log(p_module VARCHAR2, p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 獨立提交，不影響主交易
  BEGIN
    INSERT INTO debug_log(module, message) VALUES (p_module, p_msg);
    COMMIT;
  END;

  PROCEDURE log_clob(p_module VARCHAR2, p_msg CLOB) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO debug_log(module, message) VALUES (p_module, SUBSTR(p_msg, 1, 4000));
    -- 超過 4000 需分段或改用 CLOB 欄位
    COMMIT;
  END;
END;
/

方法三：UTL_FILE — 檔案級海量輸出

前置準備

-- 1. 建立目錄物件 (需 CREATE ANY DIRECTORY 權限)
CREATE OR REPLACE DIRECTORY debug_dir AS '/u01/app/oracle/admin/debug';
GRANT READ, WRITE ON DIRECTORY debug_dir TO dev_user;

-- 2. 確保 OS 目錄存在且 oracle 使用者可讀寫

寫入範本

DECLARE
  fh   UTL_FILE.FILE_TYPE;
  v_msg VARCHAR2(32767) := '大量除錯資料...';
BEGIN
  fh := UTL_FILE.FOPEN('DEBUG_DIR', 'debug_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log', 'W', 32767);
  UTL_FILE.PUT_LINE(fh, v_msg);
  -- 迴圈中大量寫入建議使用 PUT 避免頻繁換行刷新
  UTL_FILE.FCLOSE(fh);
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20001, '目錄路徑無效或未建立 Directory 物件');
  WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20002, '寫入失敗，檢查磁碟空間/權限');
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(fh) THEN UTL_FILE.FCLOSE(fh); END IF;
    RAISE;
END;
/

三法對比與選型建議

進階技巧

• 條件式除錯：封裝中加入 IF debug_pkg.is_enabled(module) THEN ... END IF;，透過參數表動態開關模組，零程式碼部署切換。
• 結構化日誌：暫存表加入 call_stack ( DBMS_UTILITY.FORMAT_CALL_STACK )、bind_vars (JSON 格式) 欄位，還原完整執行上下文。
• UTL_FILE 非同步：大量寫入時，考慮 DBMS_SCHEDULER 背景作業卸載 I/O，避免阻塞前台會話。

掌握三大法並按場景組合，讓 PL/SQL 除錯從「盲人摸象」變為「全景可視」。