Best way to Rollback if a COMMIT fails

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

How do I rollback if the COMMIT statement fails?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor Commented:
Thanks Mark. Hey what about this :

EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
Mark GeerlingsDatabase AdministratorCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Geert GOracle dbaCommented:
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
slightwv (䄆 Netminder) 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 AdministratorCommented:
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 dbaCommented:
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
slightwv (䄆 Netminder) 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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.