1 Reply Latest reply on Aug 22, 2006 12:26 AM by 23650

    Bulk Errors Exception Handling

    430487
      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?