Best way to Rollback if a COMMIT fails

brgdotnet
brgdotnet used Ask the Experts™
on
I have a large stored procedure and at the end I have a  "COMMIT;" statement.

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Commented:
The simple answer to your question is to add these two lines to the end of your procedure just before the "end;" line:
exception
  rollback;

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.
brgdotnetcontractor

Author

Commented:
Thanks Mark. Hey what about this :

EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
Mark GeerlingsDatabase Administrator

Commented:
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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Geert GOracle dba
Top Expert 2009

Commented:
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 2018
Commented:
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

https://docs.oracle.com/database/122/CNCPT/data-integrity.htm#CNCPT422
Mark GeerlingsDatabase Administrator
Commented:
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
Commented:
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
https://docs.oracle.com/database/121/SQLRF/statements_10001.htm#SQLRF01701
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>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?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial