Oracle 存儲過程 DBMS_OUTPUT 緩衝區溢位錯誤 (ORA-20000/ORU-10027) 解決方案

前言

存儲過程中大量呼叫 DBMS_OUTPUT.PUT_LINE 進行除錯輸出，若超過預設 10,000 位元組緩衝區限制，將拋出 ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes。本文分析成因，提供三種解決方案，並說明最佳除錯輸出實踐。

一、錯誤完整堆疊

ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 18

二、根因分析

• DBMS_OUTPUT 套件預設緩衝區大小 10,000 位元組 (約 10KB)。
• 迴圈中重複呼叫 PUT_LINE 累積輸出，超過限制即觸發 ORU-10027。
• 此緩衝區屬於 Session 級別，不影響其他 Session。

三、三種解決方案 (按推薦度)

方案 1：會話級調大緩衝區 (最快、最常用)

-- 在執行存儲過程前，於同一 Session 執行
SET SERVEROUTPUT ON SIZE 1000000;  -- 1MB，最大 1,000,000 (10g) / 無上限 (11g+)
-- 或
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);  -- 程式化啟用
END;
/

• SQL*Plus / SQL Developer / PL/SQL Developer 皆支援 SET SERVEROUTPUT ON SIZE ...。
• 10g 最大 1,000,000；11gR2 起 SIZE UNLIMITED 可移除上限。

方案 2：改寫程式邏輯，避免迴圈內逐行輸出 (根治)

反模式：

FOR r IN (SELECT * FROM big_table) LOOP
    DBMS_OUTPUT.PUT_LINE('Processing ' || r.id);  -- 萬次迴圈必爆
    -- 業務邏輯
END LOOP;

正模式：

-- 1. 累積在集合中，迴圈外一次輸出
DECLARE
    TYPE vc_arr IS TABLE OF VARCHAR2(4000);
    v_msgs vc_arr := vc_arr();
BEGIN
    FOR r IN (SELECT * FROM big_table) LOOP
        v_msgs.EXTEND;
        v_msgs(v_msgs.LAST) := 'Processing ' || r.id;
        -- 業務邏輯
    END LOOP;
    -- 批次輸出 (仍受緩衝區限制，但可控)
    FOR i IN 1..v_msgs.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_msgs(i));
    END LOOP;
END;

更佳：改用日志表或 UTL_FILE：

-- 寫入日志表 (不受 DBMS_OUTPUT 限制，可查詢、保留、分析)
INSERT INTO debug_log (run_id, msg, created_at) VALUES (v_run_id, 'Processing ' || r.id, SYSTIMESTAMP);

-- 或寫入作業系統檔案 (UTL_FILE 需目錄物件權限)
DECLARE
    f UTL_FILE.FILE_TYPE;
BEGIN
    f := UTL_FILE.FOPEN('LOG_DIR', 'proc_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.log', 'A');
    UTL_FILE.PUT_LINE(f, 'Processing ' || r.id);
    UTL_FILE.FCLOSE(f);
END;

方案 3：條件式除錯輸出 (按需開啟)

-- 封裝除錯開關
CREATE OR REPLACE PACKAGE debug_pkg AS
    g_enabled BOOLEAN := FALSE;
    PROCEDURE log(p_msg VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY debug_pkg AS
    PROCEDURE log(p_msg VARCHAR2) IS
    BEGIN
        IF g_enabled THEN
            DBMS_OUTPUT.PUT_LINE(p_msg);
        END IF;
    END;
END;
/

-- 業務代碼
BEGIN
    debug_pkg.g_enabled := TRUE;  -- 或從配置表讀取
    FOR r IN (...) LOOP
        debug_pkg.log('Processing ' || r.id);
    END LOOP;
END;

四、工具端配置備忘

五、最佳實踐總結

1. 生產代碼禁用 DBMS_OUTPUT：除錯輸出會消耗 PGA、增加上下文切換、可能導致緩衝區溢位中斷主流程。
2. 採用日志表 + 等級 (DEBUG/INFO/WARN/ERROR)：可查詢、可歸檔、可監控、不影響效能。
3. 開發/測試階段：SET SERVEROUTPUT ON SIZE 1000000 為標準動作。
4. 大量輸出場景：改用 UTL_FILE 寫檔或外部表，避免佔用資料庫記憶體。