(PL)SQL - Rollback to Savepoint

Search For

A simple example of how to use rollback inside a loop, to catch & handle all exceptions but still process all the 'good' iterations.
The code below does not do anything really useful, but is shows the principle. For i=1/3/5 processing will continue, for i=2/4/6/ the forced (1/0) exception is caught and handled gracefully.

Running the demo procedure will result in the following exception:

ORA-20000: v_errors= i=2 i=4 i=6
ORA-06512: at "TEDSTRUIK.TED_P1092", line 40

And the dbms_output will be:

i=1
savepoint_01

i=2
savepoint_01
1/0
exception
rollback
v_errors= i=2

i=3
savepoint_01

i=4
savepoint_01
1/0
exception
rollback
v_errors= i=2 i=4

i=5
savepoint_01

i=6
savepoint_01
1/0
exception
rollback
v_errors= i=2 i=4 i=6

You can change the dbms_output to some real log entries, and/or choose not to raise the exception but log that as well.

Package

Package
	Name	TED_P1092
	Source	package ted_p1092 is procedure demo; end ted_p1092;

Package Body

Package Body
	Name	TED_P1092
	Source	package body ted_p1092 is procedure demo is v_x number; v_errors varchar2(4000); begin for i in 1..6 loop dbms_output.put_line('i=' \|\| i); begin dbms_output.put_line('savepoint_01'); savepoint savepoint_01; if i in (2,4,6) then -- force an exception dbms_output.put_line('1/0'); v_x := 1/0; end if; exception when others then dbms_output.put_line('exception'); rollback to savepoint_01; dbms_output.put_line('rollback'); -- add text to v_errors, making sure v_errors doesn't grow too much if nvl(length(v_errors), 0) < 3900 then v_errors := v_errors \|\| ' i=' \|\| trim(i); else if v_errors not like '%[...]' then v_errors := v_errors \|\| ' [...]'; end if; end if; dbms_output.put_line('v_errors='\|\|v_errors); end; dbms_output.put_line(''); end loop; if v_errors is not null then raise_application_error(-20000, 'v_errors=' \|\| v_errors); end if; end demo; end ted_p1092;

Rollback to Savepoint

Code

Package

Package Body