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