Oracle自治事务存储过程保留EXCEPTION是否推荐？主事务需独立执行

核心结论 #

必须保留自治事务存储过程pr_call_log中的EXCEPTION块，且异常处理的位置是正确的，但当前的异常处理逻辑存在严重缺陷，需要优化。

为什么必须保留EXCEPTION块 #

你的核心需求是主DML操作不受触发器/存储过程错误影响，且错误信息能被捕获，而自治事务的异常如果不处理，会直接向上传播到调用它的触发器：

• 若触发器已处于EXCEPTION处理流程中（比如处理app_txn_audit插入失败的错误），此时存储过程抛出未处理的异常，会绕过触发器的异常捕获逻辑，直接导致主DML操作回滚，完全违反你“主DML正常完成”的要求。
• 保留存储过程内部的EXCEPTION块，能将存储过程的错误完全隔离在自治事务内部，不会影响主事务的执行。

当前异常处理的问题 #

你现在的WHEN OTHERS THEN NULL;逻辑会直接丢失存储过程自身的错误信息：

• 比如app_log表插入失败（字段长度不足、表不存在、权限不够等），你无法感知到这个错误发生，不符合“错误信息被捕获”的需求。
• 绝对不能用NULL直接忽略异常，否则审计/日志机制本身的失效会完全隐蔽，无法排查问题。

优化后的异常处理方案 #

需要在存储过程的EXCEPTION块中，尝试将自身的错误也记录下来，兜底逻辑确保不向外抛出异常。示例代码如下：

CREATE OR REPLACE PROCEDURE pr_call_log (
    v_sqlcode NUMBER,
    v_sqlerrm VARCHAR2
) IS
    pragma autonomous_transaction;
    v_self_err_msg VARCHAR2(4000);
BEGIN
    INSERT INTO app_log (sqlcode, sqlerrm) VALUES (v_sqlcode, v_sqlerrm);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 拼接存储过程自身的错误信息
        v_self_err_msg := 'pr_call_log执行失败: ' || SQLERRM 
                          || ', 原错误码: ' || v_sqlcode 
                          || ', 原错误信息: ' || v_sqlerrm;
        -- 尝试写入备用日志表
        BEGIN
            INSERT INTO app_log_fallback (error_msg, log_time) 
            VALUES (v_self_err_msg, SYSDATE);
            COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
                -- 备用表也不可用时，尝试写入操作系统文件（需提前配置UTL_FILE_DIR权限）
                DECLARE
                    v_file UTL_FILE.FILE_TYPE;
                BEGIN
                    v_file := UTL_FILE.FOPEN('DB_LOG_DIR', 'audit_error.log', 'A');
                    UTL_FILE.PUT_LINE(v_file, SYSDATE || ': ' || v_self_err_msg);
                    UTL_FILE.FCLOSE(v_file);
                EXCEPTION
                    WHEN OTHERS THEN
                        -- 最后兜底，确保不抛出异常影响主事务
                        NULL;
                END;
        END;
    COMMIT;
END;
/

额外的语法修正提示 #

你的现有代码存在两处语法错误，会导致不必要的异常：

1. 触发器中变量声明重名：v_sqlerrm varchar2(4000);和v_sqlerrm NUMBER;，应改为v_sql_code NUMBER;；
2. 调用pr_call_log时参数顺序错误：存储过程定义是(v_sqlcode number, v_sqlerrm VARCHAR2)，触发器中传的是(v_sqlerrm,v_sql_code)，会导致类型不匹配错误。

内容的提问来源于stack exchange，提问作者synccm2012