We help IT Professionals succeed at work.

Best way to Rollback if a COMMIT fails

brgdotnet asked
I have a large stored procedure and at the end I have a  "COMMIT;" statement.

How do I rollback if the COMMIT statement fails?
Watch Question

Database Administrator
The simple answer to your question is to add these two lines to the end of your procedure just before the "end;" line:

It is rare for a commit in an Oracle procedure to fail. Usually, if an Oracle  PL\SQL procedure fails, it is on a SQL statement (insert, update or delete) or a query and not on a commit. And usually when an Oracle procedure fails due to an error, the rollback is automatic.  You can control what happens though in Oracle procedures by adding a "begin", "exception" and "end;" section around each SQL statement or query that may fail.  Then you can control exactly what happens, like an explicit rollback of everything, plus inserting a record into an error log table and committing that, or whatever you want to happen.


Thanks Mark. Hey what about this :

Mark GeerlingsDatabase Administrator

You are correct, the "WHEN OTHERS THEN" words are also required.  (I was distracted when I posted that response yesterday.)

But, I still think it is very rare for an Oracle procedure to fail on a "commit".  Failures usually happen on other statements in the procedure.
Geert GOracle dba
Top Expert 2009

a commit never fails. (unless you can halt the database at that split second)
it's getting to the commit which can fail

an error is returned upon executing a statement
not when committing

technically, you don't commit inside the procedure
this should be done by the calling program

if this procedure is only part of something, you would commit at the complete end
not in the middle
Most Valuable Expert 2012
Distinguished Expert 2019
I don't want to overly confuse the question but wanted to point out that a commit can generate an error when the DML doesn't:  Deferred constraints.

defer checking of this constraint until a COMMIT statement is issued

Mark GeerlingsDatabase Administrator
Geert: PL\SQL allows commits in procedures, so technically this is possible.  I agree with you though that usually the commit should be handled by the calling program, but in some cases a commit in a procedure is helpful.

And, yes, slightwv is correct that a deferred constraint could cause an error to be reported at commit time, instead of when a SQL statement was executed in a procedure, but the use of deferred constraints are not common in most Oracle systems.
Geert GOracle dba
Top Expert 2009
forgot about that one.
it's been a year or 2 since i used deferred constraints :)

i still think the error occurs before you get to the commit line

you need to evaluate the error happening
and decide what to do

it is possible a part of the procedure has executed ok.
if an error occurs you need to decide if you want to rollback everything or just a part

savepoints can be used to indicate part of code
Most Valuable Expert 2012
Distinguished Expert 2019
>>i still think the error occurs before you get to the commit line

If the constraints aren't validated until commit, how can the error occur before the commit?