oracle-databaseplsqlora-00001ora-01400ora-02290

Oracle DML errors lacking detail


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)


Solution

  • 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