Oracle 12c Merge语句中如何将ORA-01858错误捕获至错误日志表

问题根源 #

默认情况下，LOG ERRORS子句仅捕获约束违规类错误（比如唯一键冲突），像ORA-01858这类数据类型转换错误（日期格式解析时遇到非数字字符）属于未被默认覆盖的错误类型，会直接抛出到外层EXCEPTION块，导致批处理作业中断。

解决方案 #

在LOG ERRORS后添加REJECT LIMIT UNLIMITED参数，扩展错误捕获范围，让数据转换类错误也能被写入预创建的错误日志表，同时允许Merge继续处理其他行，不中断作业。

修改后的代码示例 #

MERGE INTO table1
USING (
    SELECT columns FROM table2 -- 临时 staging 表
) src
-- 必须明确Merge的匹配条件，根据实际业务逻辑调整
ON (table1.primary_key_col = src.primary_key_col)
WHEN MATCHED THEN
    UPDATE SET 
        table1.col1 = src.col1,
        table1.col2 = src.col2,
        -- 列出所有需要更新的列
        table1.date_col = src.date_col -- 假设这是触发ORA-01858的日期列
WHEN NOT MATCHED THEN
    INSERT (col1, col2, date_col) -- 明确插入列
    VALUES (src.col1, src.col2, src.date_col) -- 对应源表值
-- 添加REJECT LIMIT UNLIMITED，捕获数据转换类错误并写入日志
LOG ERRORS INTO err$_myerroortable ('Merge batch log') REJECT LIMIT UNLIMITED;

-- 保留EXCEPTION块处理其他未被LOG ERRORS捕获的严重错误
EXCEPTION
    WHEN OTHERS THEN
        v_message_type := 'E';
        v_message_text := 'Merge pur pol shop data process failed'
                          || ' with error '
                          || SQLERRM;
        conadm.refresh_conshop_to_shop.insert_appl_log(v_job_name,
                                                      v_package_name,
                                                      v_procedure_name,
                                                      v_message_text,
                                                      v_message_type);

关键说明 #

1. 匹配条件必须明确：Merge语句的ON子句是语法必填项，原伪代码缺失，需根据业务逻辑补充主键或唯一匹配字段。
2. REJECT LIMIT的作用：
   • 该参数指定允许被记录的错误行数上限，UNLIMITED表示无限制。
   • 同时，它会让Oracle扩展LOG ERRORS的捕获范围，包含数据转换、格式解析类错误（比如ORA-01858）。
3. 错误日志表验证：确保错误日志表是基于目标表table1创建的，若之前创建不匹配，重新执行：

   EXEC DBMS_ERRLOG.create_error_log(dml_table_name => 'TABLE1', err_log_table_name => 'ERR$_MYERRORTABLE');
   

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