Link to home
Create AccountLog in
Avatar of brgdotnet
brgdotnetFlag 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?
ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of brgdotnet

ASKER

Thanks Mark. Hey what about this :

EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
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.
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
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account