PL/SQL (MOSC)

MOSC Banner

Performance of Savepoint vs Commits when looping through large datasets

edited Sep 18, 2013 7:25AM in PL/SQL (MOSC) 10 commentsAnswered ✓
 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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center