缺乏细节的 Oracle DML 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2149324/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle DML errors lacking detail
提问by FrustratedWithFormsDesigner
I am catching errors from a bulk insert operation like this:
我从这样的批量插入操作中捕获错误：
begin
--bulk insert
forall i in v_data.first .. v_data.last save exceptions
insert into my_filter_table values v_data (i);
commit;
exception
-- catch and print the saved-up DML errors.
when X_DML_ERRORS then
declare
v_iteration number;
begin
dbms_output.put_line('');
dbms_output.put_line('DML Errors:');
for i in 1 .. SQL%BULK_EXCEPTIONS.count loop
v_iteration := SQL%BULK_EXCEPTIONS(i).error_index;
dbms_output.put_line('Iteration: '||v_iteration||' Message: '||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
end loop;
end;
end;
The output looks something like this:
输出如下所示：
Iteration: 3 Message: ORA-01400: cannot insert NULL into ()
Iteration: 4 Message: ORA-02290: check constraint (.) violated
Iteration: 8 Message: ORA-00001: unique constraint (.) violated
The fact that I'm getting errors does not bother me, as I am testing error-handling code. The problem is that the Oracle error message is not displaying the the constraint names, i.e. it displays check constraint (.) violatedbut that doesn't tell me WHICH check constraint I violated.
我收到错误的事实并不困扰我，因为我正在测试错误处理代码。问题是 Oracle 错误消息没有显示约束名称，即它显示check constraint (.) violated但并没有告诉我我违反了哪个检查约束。
Does anyone know what's up with this?
有谁知道这是怎么回事？
(Oracle version 10.2)
（Oracle 10.2 版）
回答by Daniel Emge
SQL%BULK_EXCEPTIONS(i).error_code only saves the Oracle error number. You are then using the sqlerrm function to look up the error message text. That function would have no way of knowing what constraint is being broken.
SQL%BULK_EXCEPTIONS(i).error_code 只保存 Oracle 错误号。然后，您将使用 sqlerrm 函数查找错误消息文本。该函数无法知道正在破坏什么约束。
You can call the sqlerrm function without an exception being thrown to duplicate your results.
您可以调用 sqlerrm 函数而不会抛出异常来复制您的结果。
begin
dbms_output.put_Line(sqlerrm(-1400));
dbms_output.put_Line(sqlerrm(-2290));
dbms_output.put_Line(sqlerrm(-1));
end;
Which outputs
哪些输出
ORA-01400: cannot insert NULL into ()
ORA-02290: check constraint (.) violated
ORA-00001: unique constraint (.) violated
A possible workaround would be to reexecute the failed statement in the exception handler.
一种可能的解决方法是在异常处理程序中重新执行失败的语句。
Table Def:
表定义：
create table t ( x number(1) primary key);
Code:
代码：
declare
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
TYPE t_nums is table of NUMBER;
l_nums t_nums := t_nums(1,1,10);
begin
forall i in 1..l_nums.count save exceptions
execute immediate 'insert into t values (:x)' using l_nums(i);
exception
when dml_errors then
for j in 1..sql%bulk_exceptions.count
loop
if sql%bulk_exceptions(j).error_code = 1
then
begin
execute immediate 'insert into t values (:x)'
using l_nums(sql%bulk_exceptions(j).error_index);
exception
when dup_val_on_index then
dbms_output.put_line(sqlerrm);
end;
else
dbms_output.put_line(sqlerrm(-sql%bulk_exceptions(j).error_code));
end if;
end loop;
end;
Which outputs:
哪些输出：
ORA-01438: value larger than specified precision allowed for this column
ORA-00001: unique constraint (XXXXXXXX.SYS_C00264470) violated
ORA-24381: error(s) in array DML
