Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bulk Errors Exception Handling

430487Aug 21 2006 — edited Aug 21 2006
I have

FORALL i IN tab.FIRST .. tab.LAST
SAVE EXCEPTIONS
INSERT INTO my_table VALUES tab(i);

I have an exception handler that hits for all the exceptions. I want to be able to log key values from the records I was inserting based on the rows that error'd out in the bulkbind insert.

So - I have a loop in the exception handler that references the key values for logging:

EXCEPTION
WHEN BULK_ERRORS (which is previously defined using pragma exception_init)
FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(tab(SQL%BULK_EXCEPTIONS(i).error_index).key_column);
END LOOP;

However - when I was running this - it was logging null values. I stepped through it and found out that the error_index seems to be incorrect. In my specific scenario - the bulk is inserting 20 rows that all fail. However - the error index is starting at 21 in the SQL bulk exceptions and, goes to 40. So - when i attempt to use that to log the values that error'd - they are null. What am I missing here?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 18 2006
Added on Aug 21 2006
1 comment
575 views