• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

Exception IN Oracle Procedure

If there is an exception in Oracle procedure, will it roll back or do i have to roll back explicitly?

create or replace PROCEDURE Proc()

 

IS

BEGIN

  UPDATE STMT;

  EXCEPTION

  WHEN OTHERS THEN

      ROLLBACK;

END;
0
srikanthradix
Asked:
srikanthradix
  • 3
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
an exception, per se makes that the statement itself is rolled back.
0
 
dqmqCommented:
>If there is an exception in Oracle procedure, will it roll back or do i have to roll back explicitly?

Depends on whether it is ultimately handled or not.  If the exception bubbles up to the highest level and the task aborts, then the rollback is implicit.  However, along the way, it bubbles up through an exception handler, then the rollback must be explicit.  

In other words, if on of your error handlers traps the error, then the choice to rollback or not is up to you.  In your example code, comment out the rollback statement and you do NOT get a rollback.  Comment out the entire exception block and you do.

Bottom line, if you want a rollback, always best to do it explicitly and remove all doubt.
0
 
dqmqCommented:
Consider that you may NOT want to rollback after an exception.  You have that option by trapping the exception and then escaping to the higher level block without issuing the rollback.
0
 
srikanthradixAuthor Commented:
Thanks, Can you please also look at this question?
http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_23831929.html
0
 
dqmqCommented:
I've responded to that question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now