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 (.) violated
but that doesn't tell me WHICH check constraint I violated.
Does anyone know what's up with this?
(Oracle version 10.2)
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.
You can call the sqlerrm function without an exception being thrown to duplicate your results.
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