Oracle SQLCODE 與 SQLERRM 深度解析：異常處理核心函數實戰指南

核心定位

SQLCODE 與 SQLERRM 是 PL/SQL **WHEN OTHERS 異常處理區塊**的專屬函數，分別回傳 Oracle 錯誤代碼與完整錯誤訊息，是建立健壯錯誤日誌、監控告警的基石。

語法與行為細節

標準異常處理範本

CREATE OR REPLACE PROCEDURE proc_with_logging (p_id IN NUMBER) IS
    v_err_code NUMBER;
    v_err_msg  VARCHAR2(512);
BEGIN
    -- 業務邏輯
    UPDATE critical_table SET status = 'PROCESSED' WHERE id = p_id;
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'ID 不存在: ' || p_id);
    END IF;
    COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 特定異常優先處理
        log_error('NO_DATA_FOUND', p_id, SQLCODE, SQLERRM);
    WHEN DUP_VAL_ON_INDEX THEN
        log_error('DUP_VAL_ON_INDEX', p_id, SQLCODE, SQLERRM);
    WHEN OTHERS THEN
        -- 兜底：記錄完整錯誤上下文
        v_err_code := SQLCODE;
        v_err_msg  := SQLERRM;
        log_error('OTHERS', p_id, v_err_code, v_err_msg || ' | Call Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
        RAISE;  -- 重拋給上層或讓 Job 失敗被監控捕捉
END;
/

進階應用場景

1. 錯誤代碼映射業務語義

FUNCTION get_user_friendly_msg (p_sqlcode IN NUMBER) RETURN VARCHAR2 IS
BEGIN
    CASE p_sqlcode
        WHEN -1 THEN RETURN '唯一鍵衝突，資料重複';
        WHEN -2291 THEN RETURN '父鍵不存在，外鍵違規';
        WHEN -2292 THEN RETURN '子資料存在，無法刪除父資料';
        WHEN -1400 THEN RETURN '必填欄位不可為 NULL';
        WHEN -1438 THEN RETURN '數值超出欄位精度';
        WHEN -12899 THEN RETURN '字串長度超過欄位定義';
        WHEN -20001 THEN RETURN '業務規則違規: ' || SQLERRM;
        ELSE RETURN '系統錯誤 (' || p_sqlcode || '): ' || SQLERRM;
    END CASE;
END;

2. 搭配 DBMS_UTILITY 取得完整呼叫堆疊

WHEN OTHERS THEN
    INSERT INTO error_log (err_time, err_code, err_msg, call_stack, backtrace)
    VALUES (SYSTIMESTAMP, SQLCODE, SQLERRM,
            DBMS_UTILITY.FORMAT_ERROR_STACK,
            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    -- 不要吞掉異常！除非有極強理由
    RAISE;

3. 使用者自訂異常最佳實踐

DECLARE
    e_insufficient_funds EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_insufficient_funds, -20100);  -- 固定代碼利於追蹤
BEGIN
    IF balance < amount THEN
        RAISE_APPLICATION_ERROR(-20100, '餘額不足: ' || balance || ' < ' || amount);
    END IF;
EXCEPTION
    WHEN e_insufficient_funds THEN
        -- SQLCODE = -20100, SQLERRM 含自訂訊息
        log_biz_error('INSUFFICIENT_FUNDS', SQLCODE, SQLERRM);
END;

常見誤區與避坑

1. 在非異常區塊呼叫：若在 BEGIN...EXCEPTION 外呼叫，SQLCODE 永遠回傳 0，SQLERRM 回傳正常完成訊息 — **毫無意義**。
2. 吞掉異常不重拋：WHEN OTHERS THEN NULL; 是反模式，導致錯誤靜默消失，除非在最頂層入口點統一處理並記錄。
3. 錯誤訊息截斷：SQLERRM 僅 512 位元組，長堆疊需用 DBMS_UTILITY.FORMAT_ERROR_STACK。
4. 忽略 NO_DATA_FOUND 的 +100：若用 SQLCODE < 0 判斷是否為錯誤，會漏掉此例外。

結語

SQLCODE/SQLERRM 是 PL/SQL 錯誤處理的「聽診器」。**規範：特定異常優先捕捉 → OTHERS 記錄完整上下文 → 重拋或轉換為業務異常**。建立統一 LOG_ERROR 套件，納入專案標準，讓每個錯誤都可追溯、可告警、可分析。