Performance of Savepoint vs Commits when looping through large datasets
We are running Oracle 11g (11.2.0.1.0) database and I have a PL/SQL process that loops through records in an interface table, calls a API for each record, depending on the success of the API, additional processing occurs.
I am wondering what the recommendations from others who have done this sort of thing on using commit and rollback if there is a failure from API vs Savepoint rollback?
Simple code example
Option with Commit/Rollback
Loop through table
Call API (l_item, l_status, l_message);
if l_status != 'S' then
rollback;
end if;
update table with status;
commit;
end loop;
0