调试 Oracle 批量 DML 错误 - 如何确定哪个值导致了错误？

发布于 2024-12-16 12:28:16 字数 1621 浏览 1 评论 0原文

我正在尝试调试在一种环境中发生的 DML 错误，但在其他环境中则没有。设置是这样的：

我有大约 10 组 DML 操作（从远程表批量获取，批量插入本地表），其中两个给出 DML 错误，“无效数字”风格，另一个是“PL/ SQL：数字或值错误：批量绑定：截断绑定”。

我尝试将数据复制到“良好”的环境，但无法重现相同的错误。因此，我现在正在尝试构建一个更强大的错误处理程序来捕获这些错误并帮助查明哪些数据给我带来了问题。

我所得到的看起来像这样：

...
loop
    fetch c_some_data bulk collect into v_arr limit v_limit_size;

    forall i in 1..v_arr.count SAVE EXCEPTIONS
    insert into table_abc_1 values v_arr(i);
    exit when c_some_data%notfound;
end loop;
...
exception
    when X_DML_ERRORS then
        declare
            j integer;
        begin
            FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
                begin
                   /*only print the KEY fields for this table,
                   this table has (I think) close to 20 fields*/
                   dbms_output.put_line('DML Bulk-operation error: ' ||
                                                         ' Error Code: '||SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).stage||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).id||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).code||'; ')

                end;

            end loop;
        end;
    when others then
        PKG_EXCPTN.sp_generic_exception_handler;
end;

我确信我可以打印所有列，但是表很大（有些有超过 20 列），并且有 10 个代码块可能存在此问题。它还需要有人查看消息中的每个值并查看它是否与列类型匹配（varchar2 与数字）。

有没有一种简单的方法来打印发生错误的字段以及导致错误的值？

（使用Oracle 10g）

原文

I am trying to debug a DML error that is happening in one environment, but not others. The set up is this:

I have about 10 sets of DML operations (bulk-fetch from remote table, bulk-insert into local table), and two of them give DML errors, "Invalid Number" flavour and the other is "PL/SQL: numeric or value error: Bulk Bind: Truncated Bind".

I tried copying the data to a "good" environment and could not reproduce the same error. So I'm now trying to build a more robust error handler to catch these and help pinpoint which piece of data is giving me problems.

What I have looks like this:

...
loop
    fetch c_some_data bulk collect into v_arr limit v_limit_size;

    forall i in 1..v_arr.count SAVE EXCEPTIONS
    insert into table_abc_1 values v_arr(i);
    exit when c_some_data%notfound;
end loop;
...
exception
    when X_DML_ERRORS then
        declare
            j integer;
        begin
            FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
                begin
                   /*only print the KEY fields for this table,
                   this table has (I think) close to 20 fields*/
                   dbms_output.put_line('DML Bulk-operation error: ' ||
                                                         ' Error Code: '||SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).stage||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).id||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).code||'; ')

                end;

            end loop;
        end;
    when others then
        PKG_EXCPTN.sp_generic_exception_handler;
end;

I'm sure I could just print all of the columns, but the tables are large (some with > 20 columns) and there are 10 blocks of code that could potentially have this problem. It would also require someone to look at each value in the message and see if it matches with the column type (varchar2 vs. number).

Is there an easy way to print the field on which the error occurred and the value that caused the error?

(using Oracle 10g)

分享到QQ

分享到微博