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:
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:
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:
A few things to note about
rollback to savepoint xdoesn’t need to be called in the
exceptionblock. 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.