Oracle 存儲過程錯誤日誌最佳實踐：自治交易 + 統一記錄套件

Oracle 存儲過程錯誤日誌最佳實踐：自治交易 + 統一記錄套件

當應用調用數十上百個存儲過程，排查錯誤極其痛苦。將錯誤集中寫入資料表，搭配自治交易確保日誌不隨主交易回滾而消失，是工程化的標準做法。

1. 建立錯誤日誌表

DROP TABLE PUB_PROC_ERR_LOG PURGE;
CREATE TABLE PUB_PROC_ERR_LOG (
  LOG_ID       NUMBER PRIMARY KEY,
  MODULE_NAME  VARCHAR2(100),
  PROC_NAME    VARCHAR2(100),
  ERR_TIME     DATE DEFAULT SYSDATE,
  SQL_CODE     VARCHAR2(50),
  SQL_ERRM     VARCHAR2(100),
  ERR_CONTENT  VARCHAR2(500)
);
COMMENT ON COLUMN PUB_PROC_ERR_LOG.LOG_ID       IS '主鍵';
COMMENT ON COLUMN PUB_PROC_ERR_LOG.MODULE_NAME  IS '模組名稱';
COMMENT ON COLUMN PUB_PROC_ERR_LOG.PROC_NAME    IS '存儲過程名稱';
COMMENT ON COLUMN PUB_PROC_ERR_LOG.ERR_TIME     IS '報錯時間';
COMMENT ON COLUMN PUB_PROC_ERR_LOG.SQL_CODE     IS 'SQLCODE';
COMMENT ON COLUMN PUB_PROC_ERR_LOG.SQL_ERRM     IS 'SQLERRM';
COMMENT ON COLUMN PUB_PROC_ERR_LOG.ERR_CONTENT  IS '報錯具體行 (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE)';

2. 建立序列

CREATE SEQUENCE SEQ_RECORD_PROC_ERR
  MINVALUE 1 MAXVALUE 9999999999999999999999999999
  START WITH 21 INCREMENT BY 1 CACHE 20;

3. 通用記錄錯誤存儲過程 (自治交易)

CREATE OR REPLACE PROCEDURE RECORD_PROC_ERR_LOG (
  P_MODULE_NAME IN VARCHAR2,
  P_PROC_NAME   IN VARCHAR2,
  P_SQLCODE     IN VARCHAR2,
  P_SQLERRM     IN VARCHAR2,
  P_ERR_LINE    IN VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 關鍵：獨立提交，不受主交易回滾影響
BEGIN
  INSERT INTO PUB_PROC_ERR_LOG (LOG_ID, MODULE_NAME, PROC_NAME, ERR_TIME, SQL_CODE, SQL_ERRM, ERR_CONTENT)
  VALUES (SEQ_RECORD_PROC_ERR.NEXTVAL, P_MODULE_NAME, P_PROC_NAME, SYSDATE, P_SQLCODE, P_SQLERRM, P_ERR_LINE);
  COMMIT;
END RECORD_PROC_ERR_LOG;
/

4. 業務存儲過程調用範例

CREATE OR REPLACE PROCEDURE PROCE_TEST IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM TEST2 WHERE';  -- 故意語法錯誤
EXCEPTION
  WHEN OTHERS THEN
    RECORD_PROC_ERR_LOG(
      '模組名',
      'PROCE_TEST',
      SQLCODE,
      SQLERRM,
      SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 400)  -- 取得錯誤堆疊行號
    );
    -- 若為 Java 調用需拋出異常，取消註解下行：
    -- RAISE;
END PROCE_TEST;
/

5. 測試驗證

SQL> EXEC PROCE_TEST;
BEGIN PROCE_TEST(); END;
*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "LCAMTEST.PROCE_TEST", line 9
ORA-06512: at line 1

SQL> SELECT * FROM PUB_PROC_ERR_LOG;
LOG_ID MODULE_NAME PROC_NAME   ERR_TIME          SQL_CODE SQL_ERRM                       ERR_CONTENT
------ ----------- ----------- ----------------- -------- ------------------------------ ----------------------------------------
10     模組名       PROCE_TEST  04-八月 -15       -936     ORA-00936: missing expression  ORA-06512: at "LCAMTEST.PROCE_TEST", line 4

關鍵點總結：
1. PRAGMA AUTONOMOUS_TRANSACTION 讓日誌寫入獨立提交。
2. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 精確定位錯誤行號 (優於 DBMS_UTILITY.FORMAT_ERROR_STACK)。
3. 統一錯誤碼、錯誤訊息、模組/過程名，便於監控平台告警與趨勢分析。
4. 生產環境建議加入 JOB_ID/SESSION_ID 等上下文欄位。