BULK COLLECT FORALL ROLLBACK

Source


OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;

FORALL i IN 1 .. vrt_dept_id.COUNT
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);

EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
...


Rollback behavior
If one of the bulk DML statements fails, the individual statement is rolled back and previous DML statements within the FORALL series of statements are not rolled back and the FORALL statement stops executing.
For example if you are using FORALL to insert 100 rows and an error appears on 78th row the execution will stop and rows from 78 to 100 will not be inserted indeed. If you want to overcome this behavior and to insert all rows you can use the SAVE EXCEPTIONS clause.
With the SAVE EXCEPTIONS the cursor attribute SQL%BULK_EXCEPTIONS is used. It is an array that records two elements ERROR_INDEX and ERROR_CODE:

...
v_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (v_bulk_errors, -24381);
…
BEGIN -- outer BEGIN
…
BEGIN –- inner BEGIN clause
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;

FORALL i IN 1 .. vrt_dept_id.COUNT
SAVE EXCEPTIONS
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);

EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;

EXCEPTION WHEN bulk_errors THEN
FOR i in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
Dbms_output.put_line(
'An error '|| i ||' was occured ' ||
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
' during update of dept_id: '||
vrt_dept_id(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)||
'. Oracle error: ' ||
SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;

END; -- end inner BEGIN

In this case the collection variables will be populated on every 1000 rows, after that the FORALL will be executed for them and then will be fetched the next 1000. Be aware of that the previous values for the collection are overwritten.
The other workaround to cope with memory problems coming from large PL/SQL tables is to execute the following supplied stored procedure inside your code:

..
dbms_session.free_unused_user_memory;
..

It will free unused memory for your session after large portions of data was processed. For full details about solving of memory problems with this procedure, see: Procedure DBMS_SESSION.FREE_UNUSED_USER_MEMORY Specification
You can use the SQL%BULK_ROWCOUNT attribute, which is available with the FORALL statement. For each processed row in FORALL there is a corresponding row for this attribute. For example, if you want to know did the n-th processed row from FORALL have affected some rows then you must call SQL%BULK_ROWCOUNT(n). If no rows were affected, the n-th row will be zero.