sqlplsqloracle-sqldeveloperbulk-collect

[PLSQL]How to know the exact row which raise SAVE EXCEPTION in BULK COLLECT


I'm trying to update rows from a bulk collect with limit using forall statement. However I couldn't get the exact row which raise the exception.

    -- Cursor which get the rows to update

    -- Creation of the table TAB_REQ

BEGIN

    OPEN  c_REQ;
   LOOP
      FETCH c_REQ BULK COLLECT INTO TAB_REQ LIMIT 50000;
   BEGIN

   FORALL ii in 1 .. TAB_REQ.count SAVE EXCEPTIONS
      
   -- Update statement where exception will be raise at id=164588

   EXCEPTION
      WHEN OTHERS
      THEN
        l_errors := sql%BULK_EXCEPTIONS.count;
            FOR i IN 1 .. l_errors
            LOOP
               l_errno := sql%BULK_EXCEPTIONS(i).ERROR_CODE;
               l_msg := sqlerrm(-l_errno);
               l_idx := sql%BULK_EXCEPTIONS(i).ERROR_INDEX;
               DBMS_OUTPUT.PUT_LINE(l_errno||' : '||l_msg||' - '||l_idx||', id_ext: '|| TAB_REQ(i).id_ext);
            END LOOP;
      END;
    exit when TAB_REQ.COUNT =0;
 END LOOP;
CLOSE c_REQ;

The result is that the id printed out in exception does not correspond to the id that raise error in update loop, and the number of rows updated has 50000 less, while is exactly the limit number in bulk collect.

Anyone knows the raison?


Solution

  • You need to use sql%bulk_exceptions. You can see an example here. Keep in mind the errors are generic (meaning for errors such as "cannot insert null into X" you won't get the column name when using sqlerrm.).

    To catch the errors you must define the exception before the execution block (or at the package level), then handle it:

    DECLARE
        e_forall EXCEPTION;
        PRAGMA EXCEPTION_INIT (e_forall, -24381);
    BEGIN
        ... bulk insert code that generates the exception ...
    
    EXCEPTION 
        WHEN e_forall THEN
            FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT  
              LOOP  
                 DBMS_OUTPUT.put_line ('Error '  
                    || ' on index '  
                    || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX  
                    || SQLERRM (-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));  
              END LOOP;  
    
    
    END;
    /
    

    What does the code generate on your side that prevents you from seeing the error/cause?

    UPDATE:

    OK, now that you updated the code + gave the output I think I found it.

    The problem is here: TAB_REQ(i).id_ext

    Try: TAB_REQ(l_idx).id_ext

    ERROR_INDEX obtained from the array index is the actual record id, not the array index. So using i on the record will always return a wrong record. You need the index recovered from i.