No Oracle DML Errors

Question

I break the errors from the bulk insert operation as follows:

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 result 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 am getting errors does not bother me, as I am testing the error handling code. The problem is that the Oracle error message does not display the constraint names, that is, it displays check constraint (.) violated, but that does not tell me WHICH the check constraint that I violated.

Does anyone know what is wrong with this?

(Oracle version 10.2)

Answer 1

SQL% BULK_EXCEPTIONS (i).error_code Oracle. sqlerrm . , .

sqlerrm , .

begin
   dbms_output.put_Line(sqlerrm(-1400));
   dbms_output.put_Line(sqlerrm(-2290));
   dbms_output.put_Line(sqlerrm(-1));
end;

ORA-01400: cannot insert NULL into ()
ORA-02290: check constraint (.) violated
ORA-00001: unique constraint (.) violated

.

:

create table t ( x number(1) primary key);

:

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;

:

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