Oracle DML errors lacking detail

Question

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)

Answer 1

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