Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, hari.

Asked: August 26, 2016 - 10:54 am UTC

Last updated: August 26, 2016 - 3:28 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

In our application, we have set the commit frequency as 50 and in one case one record fails to get inserted (due to a constraint violation exception).
So, in that case, would all the 50 records in that commit slot get rolled back?

Please help

Thanks,
Hari

and Chris said...

When you rollback, everything since your last commit or savepoint will be lost.

I'm guessing you mean you have some form of loop that commits when you've had 50 iterations. e.g.

for i in 1 .. iterations loop
  insert into ...
  if mod(i, 50) = 0 then
    commit;
  end if;
end loop;
|

You can avoid this by placing a savepoint before the insert. Then rolling-back to this when you have the exception:

for i in 1 .. iterations loop
  savepoint pre_ins; 
  begin
    insert into ...
  exception
    when dup_val_on_index then
      rollback to savepoint;
  end;
  if mod(i, 50) = 0 then
    commit;
  end if;
end loop;


Or, if your values come from a select statement:

Get rid of your loop and insert everything in one go! e.g.

insert into table 
  select * from ...


If you need to bypass exceptions, check out the "log errors" clause:

https://www.youtube.com/watch?v=8WV15BzQIto

Is this answer out of date? If it is, please let us know via a Comment