CODEWITHSUNDEEP
oracle-databaseerror-handlingplsqloracle10gbulkinsert
FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27526

Debugging Oracle bulk DML error - how to determine which value caused the error?

I am trying to debug a DML error that is happening in one environment, but not others. The set up is this:

I have about 10 sets of DML operations (bulk-fetch from remote table, bulk-insert into local table), and two of them give DML errors, "Invalid Number" flavour and the other is "PL/SQL: numeric or value error: Bulk Bind: Truncated Bind".

I tried copying the data to a "good" environment and could not reproduce the same error. So I'm now trying to build a more robust error handler to catch these and help pinpoint which piece of data is giving me problems.

What I have looks like this:

...
loop
    fetch c_some_data bulk collect into v_arr limit v_limit_size;

    forall i in 1..v_arr.count SAVE EXCEPTIONS
    insert into table_abc_1 values v_arr(i);
    exit when c_some_data%notfound;
end loop;
...
exception
    when X_DML_ERRORS then
        declare
            j integer;
        begin
            FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
                begin
                   /*only print the KEY fields for this table,
                   this table has (I think) close to 20 fields*/
                   dbms_output.put_line('DML Bulk-operation error: ' ||
                                                         ' Error Code: '||SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).stage||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).id||'; '||
                                                         v_arr(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).code||'; ')

                end;

            end loop;
        end;
    when others then
        PKG_EXCPTN.sp_generic_exception_handler;
end;

I'm sure I could just print all of the columns, but the tables are large (some with > 20 columns) and there are 10 blocks of code that could potentially have this problem. It would also require someone to look at each value in the message and see if it matches with the column type (varchar2 vs. number).

Is there an easy way to print the field on which the error occurred and the value that caused the error?

(using Oracle 10g)

Upvotes: 3

Views: 3217

Answers (1)

Kevin Burton
Kevin Burton

Reputation: 11924

you could use LOG ERRORS INTO in your DML statement , this will allow you to dump your errors and bad data to a table so you can check them later.

see: http://www.oracle-developer.net/display.php?id=329

and: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBDIGAH

Upvotes: 3

Related Questions