Partial Rollbacks
The ability to rollback transactionsin PL/SQL is very helpful when something goes wrong and you want to undo what was just done. At a very high level, most code that use rollback look like the following:
begin
...
exception
when others then
rollback;
raise;
end;
The above code makes sense as the rollback occurs when an error happens. (Note for APEX users, an implicit rollback occurs in processes if an exception occurs) The caveat is that it rollsback the entire transaction (i.e. from the start). What if the code is part of a larger block of code and you only want to rollback to the previous step? The following pseudo code is an example:
begin
do_step_1;
do_step_2;
-- Step 3 may error out and if it does, still want to preserve the work of steps 1 and 2.
do_step_3;
do_step_4;
end;
In do_step_3 if a generic rollback was used in the exception block it would undo any chnages that were done in step's 1 and 2 which is not the desired outcome. Thankfully PL/SQL rollback functionality supports this by using savepoint. If we want do_step_3 to work as intented this is what it should look like:
create or replace procedure do_step_3 as
begin
savepoint start_step_3;
...
exception
when some_expected_error then
-- This will rollback all changes to the start of do_step_3
rollback to savepoint start_step_3;
-- no raise as this was an expected error
when others then
raise;
end do_step_3;
A few things to note about savepoints:
- The
rollback to savepoint xdoesn't need to be called in theexceptionblock. I've used it in other places as well. Ex:if <> then rollback to savepoint... - Try not to litter your code with
savepoints. It can get very confusing and tough to debug if you have a lot of them. The business logic will really determine when you need to use them. - In the past ten years I've only needed to use them a handful of times. It's not a common feature that you'll need to use but good to know when you need it.
Oracle documentation for savepoint can be found here.