Avatar of brgdotnet
brgdotnet
Flag for United States of America asked on

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?
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
brgdotnet

ASKER
Thanks Mark. Hey what about this :

EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
Mark Geerlings

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 G

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Geert G

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.