We are having script like this:
DELARE
CURSOR c1 IS ....;
PROCEDURE a();
BEGIN
SAVEPOINT p1;
-- do stuff
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO p1;
END a;
BEGIN
FOR c IN c1 LOOP
a();
END LOOP;
END;
The cursor has over 5000 rows.
Problem is, when we run for 5000 rows and check random rows, for the runs of PROCEDURE a() which threw an error the ROLLBACK of the inserts and updates have not happened.
Why is this?
We are using this method so that when we commit after the script executed there wont be partial inserts or updates to tables.
Why is this not working?
PS: Some records seems to be rolled back, but some are not.