This discussion is archived
1 2 3 4 Previous Next 49 Replies Latest reply: Jan 10, 2013 10:07 PM by BillyVerreynne Go to original post RSS
  • 30. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    Thank you for your careful rereading and analysis.

    I was trying to demonstrate "statement-level atomicity" and "statement-level rollback", to use terms from the Concepts Guide.

    I should have been more explicit from the beginning about what PL/SQL statements could be "atomic" (in addition to DML statements).

    As I stated when quoting Tom Kyte, I meant anonymous PL/SQL blocks (not nested blocks, functions or procedures).

    All the talk about SAVEPOINTs and rollbacks only applies to the anonymous block - which means every call to the PL/SQL engine from without.

    The rollback is an "all or nothing" proposition: if an unhandled exception comes back from the anonymous block, the caller knows any changes to the data by that block were rolled back, even if they took place in a nested block or procedure. If no exception comes back, the caller knows there was no automatic "statement-level rollback".

    When I say "the anonymous PL/SQL block is rolled back", I mean "the entire calling chain of blocks is rolled back", to borrow your more explicit phrase.

    About RAISE "causing" a statement-level rollback: I grant that the rollback only occurs if the anonymous block returns an unhandled exception. RAISE causes an exception, but it does not prevent that exception from being handled farther up the chain.

    Getting back to the original question, "Why does Tom Kyte say this is a bug?", I say it's because WHEN OTHERS without RAISE handles exceptions and therefore "turns off" statement-level rollback. This is almost always an unexpected and unfortunate side effect of the code - in other words, a bug.
  • 31. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    Rahul_India wrote:
    I am all confused now.
    If you're confused about what to do, just follow BluShadow's advice. If you would like more details on how to implement his advice, please ask a new question.

    If you're confused about all the theory, sorry!
  • 32. Re: WHEN  OTHERS is a bug
    Rahul_India Journeyer
    Currently Being Moderated
    Stew Ashton wrote:
    Rahul_India wrote:
    I am all confused now.
    If you're confused about what to do, just follow BluShadow's advice. If you would like more details on how to implement his advice, please ask a new question.

    If you're confused about all the theory, sorry!
    lol
    i understood everything on page 1..till your bank example
    But all those rants after that have confused me :p...ok so you mean BlueShadows explanation is near to perfect.
  • 33. Re: WHEN  OTHERS is a bug
    rp0428 Guru
    Currently Being Moderated
    >
    A. Because he thinks most people don't understand how to use it properly
    >
    I agree. There is at least one valid use for WHEN OTHERS even in lower-level blocks.

    An exception block can only be used to trap NAMED exceptions. Most of Oracle exceptions are not named. A lower-level block may need to trap a set of NAMED exceptions (Oracles or custom) to actually handle them. It might then also use WHEN OTHERS to log information at that level about these other exceptions and then use RAISE to pass them up the chain.
  • 34. Re: WHEN  OTHERS is a bug
    Sven W. Guru
    Currently Being Moderated
    Stew Ashton wrote:
    All the talk about SAVEPOINTs and rollbacks only applies to the anonymous block - which means every call to the PL/SQL engine from without.
    That is not entirely what happend. You didn't mention anonymous blocks at the beginning. This was the misleading part.
    The rollback is an "all or nothing" proposition: if an unhandled exception comes back from the anonymous block, the caller knows any changes to the data by that block were rolled back, even if they took place in a nested block or procedure. If no exception comes back, the caller knows there was no automatic "statement-level rollback".

    When I say "the anonymous PL/SQL block is rolled back", I mean "the entire calling chain of blocks is rolled back", to borrow your more explicit phrase.
    There is no chain of anonymous pl/sql blocks. At least none that I'm aware of. Tom speaks of blocks. You speak of anonymous blocks. Which is quite a huge difference.

    I think your confusion comes from this. Imho not pl/sql does the rollback of an anonymous pl/sql block. since there is no such rollback concept in pl/sql.
    The automatic rollback of an anonymous block is a feature of SQL*PLUS or even of the oracle database(=sql engine), how Tom tells us, but this distinction is slim. That means the tool that you used to run your pl/sql code issued an implicit savepoint, just before the block and then rolls back this block, if an error was encountered. But it is not the pl/sql engine that does it.

    >
    About RAISE "causing" a statement-level rollback: I grant that the rollback only occurs if the anonymous block returns an unhandled exception. RAISE causes an exception, but it does not prevent that exception from being handled farther up the chain.
    That is correct. Same as if you build your own application that will run Pl/sql, then you decide what to do in a case of error. The sql*plus developers decided the same.

    The pl/sql documentation (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm) states this:
    If an exception is raised in a block that has no exception handler for it, then the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a block has a handler for it or there is no enclosing block (for more information, see "Exception Propagation"). If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome (for more information, see "Unhandled Exceptions").
    Edited by: Sven W. on Dec 3, 2012 8:35 PM

    Edited by: Sven W. on Dec 3, 2012 8:52 PM - clarification and change to a less challenging message
  • 35. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    Sven W. wrote:
    Stew Ashton wrote:
    All the talk about SAVEPOINTs and rollbacks only applies to the anonymous block - which means every call to the PL/SQL engine from without.
    That is not entirely what happend. You didn't mention anonymous blocks at the beginning. This was the misleading part.
    Why do you say that? Didn't I already apologize for not making that clear? If I meant to justify my previous posts, I would have used the past tense "applied", not the present tense "applies".

    The fact is I brought up statement-level rollback, which is relevant to the original question.
    There is no chain of anonymous pl/sql blocks.
    I was using rp's expression to indicate I agreed with his analysis. If you prefer to talk about "nested blocks" or "enclosed blocks", fine with me.
    Tom speaks of blocks. You speak of anonymous blocks. Which is quite a huge difference.
    I don't know what you're getting at. In the article I quoted, Tom Kyte speaks quite clearly of "anonymous blocks".
    I think your confusion comes from this. Imho not pl/sql does the rollback of an anonymous pl/sql block. since there is no such rollback concept in pl/sql.
    The automatic rollback of an anonymous block is a feature of SQL*PLUS or even of the oracle database(=sql engine), how Tom tells us, but this distinction is slim.
    The distinction between the Oracle Database and some client code is not "slim": it is fundamental.
    That means the tool that you used to run your pl/sql code issued an implicit savepoint, just before the block and then rolls back this block, if an error was encountered. But it is not the pl/sql engine that does it.
    I don't use SQL*Plus. I use Oracle SQL Developer. I also did the same thing in Java and I committed. Both times I got the same result.

    Did I say the PL/SQL engine did the rollback? My understanding is Oracle Database does the rollback, whether the "statement" is SQL or PL/SQL.
    ...Same as if you build your own application that will run Pl/sql, then you decide what to do in a case of error....
    In my Java code, once the exception came back I committed, then queried the table: the entire anonymous PL/SQL block had been rolled back already, just as with Oracle SQL Developer.
    PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome
    of the transaction, not the statement.

    You are disagreeing with Tom Kyte and my test cases. Where is your test case?

    By the way, here's a quote from a book on this subject (though I would add the word "anonymous" now):
    PL/SQL blocks implicitly create a savepoint into the code. Thus, independent of where an error occurs in a PL/SQL block, all changes in the block are automatically rolled back to a point as if the PL/SQL routine was never called. This behavior is one of the truly great features in PL/SQL.

    Excerpt From: John Beresniewicz, Adrian Billington, Martin Büchi, Melanie Caffrey, Ron Crisco, Lewis Cunningham, Dominic Delmolino, Sue Harper, Torben Holm, Connor McDonald, Arup Nanda, Stephan Petit, Michael Rosenblum, Robyn Sands, Riyaj Shamsudeen. Expert PL/SQL Practices: for Oracle Developers and DBAs. Apress®, 2011-07-29. iBooks. This material may be protected by copyright.
  • 36. Re: WHEN  OTHERS is a bug
    Rahul_India Journeyer
    Currently Being Moderated
    So is TOM KYTE right? :b
  • 37. Re: WHEN  OTHERS is a bug
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    There are seldom absolutes in software engineering.

     
    Software is engineered to address specific requirements and solve specific problems. Requirements differ. Problem are often very unique.

    So, no a "+WHEN OTHERS+" exception is NOT a bug. It depends on WHAT requirement it addresses and WHICH problem it solves.

    Yes, most time when a developer writes a "+WHEN OTHERS+" exception it IS a bug. Because the developer does not understand the requirements, and does not know how to solve the problem, correctly.
  • 38. Re: WHEN  OTHERS is a bug
    BluShadow Guru Moderator
    Currently Being Moderated
    Rahul India wrote:
    So is TOM KYTE right? :b
    Yes, Tom is right.... but how people interpret what Tom has said may not be, for the same reason those same people think it's ok to use a WHEN OTHERS exception when clearly they shouldn't be.... they just don't understand the underlying reasons.
  • 39. Re: WHEN  OTHERS is a bug
    Rahul_India Journeyer
    Currently Being Moderated
    I think there is no one correct answer for this question
  • 40. Re: WHEN  OTHERS is a bug
    Dave Rabone Journeyer
    Currently Being Moderated
    Wrong!

    To quote Tom Kyte - the one correct answer is "it depends"
  • 41. Re: WHEN  OTHERS is a bug
    Rahul_India Journeyer
    Currently Being Moderated
    Dave Rabone wrote:
    Wrong!

    To quote Tom Kyte - the one correct answer is "it depends"
    That what i am asking on what to depend
  • 42. Re: WHEN  OTHERS is a bug
    BluShadow Guru Moderator
    Currently Being Moderated
    It depends if the developer is using it for valid and justifiable reasons... which in most cases we see on these forums, people are not.
  • 43. Re: WHEN  OTHERS is a bug
    Rahul_India Journeyer
    Currently Being Moderated
    BluShadow wrote:
    It depends if the developer is using it for valid and justifiable reasons... which in most cases we see on these forums, people are not.
    I think this is the most debatable topic in oracle pl/sql.
  • 44. Re: WHEN  OTHERS is a bug
    BluShadow Guru Moderator
    Currently Being Moderated
    Rahul India wrote:
    BluShadow wrote:
    It depends if the developer is using it for valid and justifiable reasons... which in most cases we see on these forums, people are not.
    I think this is the most debatable topic in oracle pl/sql.
    I don't. There's nothing to debate. WHEN OTHERS is a bug (from the application developers point of view, not a bug in Oracle) unless it's correctly understood and used with a RAISE for a justifiable reason (of which most developers who tend to use it can't justify it, hence they are writing code with a bug).

    There's nothing more to it than that.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points