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
  • 15. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    rp0428 wrote:
    If you have multiple blocks that should be handled differently you can use a SAVEPOINT after the first block and then if the second block causes an exception you can either rollback to the SAVEPOINT (which saves the work the first block did) or rollback the entire transaction.
    My point was that PL/SQL does that automatically. We don't have to.

    That is what I demonstrated.
  • 16. Re: WHEN  OTHERS is a bug
    rp0428 Guru
    Currently Being Moderated
    >
    If a statement fails, Oracle will roll back that statement before returning control.

    If a PL/SQL block fails, Oracle will roll back the block before returning control. Think of it as an implicit savepoint at the beginning of the block, and an automatic "rollback to savepoint" if the block fails.
    >
    Well that is certainly uncharacteristic of your normal replies. Perhaps you were thinking of something else? Or left something out of what you were trying to explain?

    Incorrect - there is NO implicit savepoint at the beginning of a block.

    As I said in my own reply to OP above you don't rollback 'blocks' - you rollback transactions. You either rollback the entire transaction or you rollback to a savepoint.

    A transaction might be a statement or multiple statements, a block or multiple blocks, a procedure or multiple procedures, etc.

    In PL/SQL a transaction begins with the first statement and ends with a ROLLBACK or a COMMIT. You can issue INSERT statements one after the other and none of them will get committed or rolled back just because you reach the end of a block.

    SQL Language Doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9021.htm#i2104635
    >
    ROLLBACK Purpose
    Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction.
  • 17. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    Steve Cosner wrote:
    Looking at Tom Kyte's statement:
    A when others is almost always a BUG unless it is immediately followed by a RAISE
    I would take a contrary position: A when others can be used to gracefully report an unexpected error.
    Writing WHEN OTHERS THEN RAISE should never be used...
    I interpret Tom Kyte's statements very differently.

    Tom Kyte has often said that WHEN OTHERS THEN RAISE is stupid, because it hides the line number where the error occurred.

    He has stated that the best (or even the only) good use of WHEN OTHERS is "to gracefully report an unexpected error".

    However, once the error is reported "gracefully", it must be RAISEd so Oracle will roll back the block and so that any and every calling program will know what happened.

    Tom and you agree that WHEN OTHERS THEN NULL is very, very bad.

    You only seem to contradict Tom on one point: Tom says the error should be logged, reported, whatever - but then RAISEd. You seem to be saying that the RAISE is unnecessary. I say it is, for the two reasons I have already explained: roll back the block, notify any and every caller in an explicit, standard manner.
  • 18. Re: WHEN  OTHERS is a bug
    rp0428 Guru
    Currently Being Moderated
    >
    However, once the error is reported "gracefully", it must be RAISEd so Oracle will roll back the block and so that any and every calling program will know what happened.
    >
    This is going to start confusing a lot of people. Raising an exception doesn't rollback anything and when a rollback is done it is the entire current transaction that is rolled back not a block or statement.

    Please explain where you got this 'roll back the block' stuff and why you think Oracle will roll anything back just because an exception is raised.

    See Transaction Management in the Database Concepts doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm
    >
    Introduction to Transactions

    A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

    A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.
  • 19. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    rp0428 wrote:
    Please explain where you got this 'roll back the block' stuff and why you think Oracle will roll anything back just because an exception is raised.
    http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61asktom-512015.html

    See the section "Why You Really Want to Let Exceptions Propagate"
    Transactions are atomic in Oracle Database, meaning that either all the statements that constitute the transaction are committed (made permanent) or all of them are rolled back ... This atomicity extends to anonymous blocks as well.
    And a bit further down:
    Oracle Database treated the stored procedure call as an atomic statement. The client submitted a block of code—BEGIN P; END;—and Oracle Database wrapped a SAVEPOINT around it. Because P failed, Oracle Database restored the database back to the point right before it was called.
    Scrolling further:
    I consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE or RAISE_APPLICATION_ERROR to reraise the exception to be a bug. It silently ignores the error, and it changes the transaction semantics.
    In other words, "doubly dangerous" as I said above.

    There is transaction-level atomicity and there is statement-level atomicity. Transaction-level atomicity is controlled by our code that says COMMIT or ROLLBACK. Statement-level atomicity is controlled by Oracle. It applies to SQL statements and anonymous PL/SQL blocks - except that in PL/SQL we can mess it up with WHEN OTHERS.

    If anyone can show me a difference between Tom's article and what I wrote in this thread, please do so. I certainly don't want to confuse anyone, including myself :)
  • 20. Re: WHEN  OTHERS is a bug
    BluShadow Guru Moderator
    Currently Being Moderated
    I think the confusion here is that rp is simply making the point that, just because you issue a RAISE in an exception block, it doesn't necessarily mean that the transaction will automatically be rolled back.

    If you have procedures calling procedures calling procedures etc. then just because the lowest call of procedure issues a RAISE, doesn't necessarily mean that any transaction has been rolled back. Yes, there may be some implicit rollbacks depending on the environment and level of call being made, but in a regular call tree within an application it's good to be clear that RAISE does not cause a rollback itself.
  • 21. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    BluShadow wrote:
    I think the confusion here is that rp is simply making the point that, just because you issue a RAISE in an exception block, it doesn't necessarily mean that the transaction will automatically be rolled back.
    Who ever said that? Where did rp claim I said that?

    I completely agree that there is no automatic rollback of a transaction. If you think people are confused about that, then of course you are right to set things straight.
    Yes, there may be some implicit rollbacks depending on the environment and level of call being made
    So we agree. I'm waiting to see if rp agrees with us.
    but in a regular call tree within an application it's good to be clear that RAISE does not cause a rollback itself.
    RAISE does not cause a transaction-level rollback.

    It does cause a statement-level rollback.
    SQL> CREATE TABLE T(N NUMBER);
    
    table T created.
    
    SQL> insert into t values(1);
    
    1 rows inserted.
    
    SQL> BEGIN
    INSERT INTO T VALUES(2);
    RAISE no_data_found;
    END;
    /
    Error starting at line 5 in command:
    BEGIN
    INSERT INTO T VALUES(2);
    RAISE no_data_found;
    END;
    Error report:
    ORA-01403: no data found
    ORA-06512: at line 3
    01403. 00000 -  "no data found"
    *Cause:    
    *Action:
    
    SQL> commit;
    
    committed.
    
    SQL> select * from t;
    N
    -
    1
    The PL/SQL block that inserted the second row got rolled back. The transaction as a whole did not get rolled back.

    How do you explain that, if RAISE didn't cause a statement-level rollback?
  • 22. Re: WHEN  OTHERS is a bug
    Dave Rabone Journeyer
    Currently Being Moderated
    Splitting hairs I suppose, but a RAISE doesn't cause the statement level rollback, the original exception does, unless it is trapped by an exception handler and not re-raised.
  • 23. Re: WHEN  OTHERS is a bug
    Stew Ashton Expert
    Currently Being Moderated
    Dave Rabone wrote:
    Splitting hairs I suppose, but a RAISE doesn't cause the statement level rollback, the original exception does, unless it is trapped by an exception handler and not re-raised.
    Well, my second test case doesn't even have an "original exception" - but so what?

    The important thing is to agree that untrapped exceptions cause statement level rollbacks, whereas trapped and unraised exceptions don't. That is the point I have been trying to make, as a partial answer to the original question.
  • 24. Re: WHEN  OTHERS is a bug
    rp0428 Guru
    Currently Being Moderated
    >
    So we agree. I'm waiting to see if rp agrees with us.
    >
    After a lot more testing and rereading of everything posted I came to the following 'tentative' conclusions

    1. Each of us is right
    2. Each of us is wrong
    3. Each of us neglected to mention at least one key aspect of this
    4. I'm still about as confused now as I was to begin with.

    First, I think I contributed to the confusion because the bulk of my comments were about what the USER does, or can do; not what Oracle itself might do on its own. Unfortunately I think most, or all, of you were commenting on what Oracle does, or does not do.

    Secondly, my viewpoint was/is from pl/sql itself not necessarily just from the top-level that a calling client sees. Procedures/functions can be called by other procedures/functions or triggers but the client only sees the top of the stack. That contributed to the confusion because at least some of the responders comments seem strictly limited to either anonymous blocks or the result that an outside client sees.

    So I'll try to revisit my own comments first and clarify where I went right/wrong. The number labels are for reference when I revisit some of these later in the reply

    #1
    My first post of Nov 30, 2012 9:28AM in reply to this comment by Rahul
    >
    so what it means that whenever we encounter an exception in the PL/SQL ..whole block should be rolled back.Right?
    >
    I interpreted the 'whole block should be rolled back' to be asking if that is what the USER should do. I think you and others were interpreting that to be asking what is Oracle going to do.

    So my reply was
    >
    NO! You don't rollback 'blocks' - you rollback transactions. You either rollback the entire transaction or you rollback to a savepoint.
    >
    I was stating that the USER doesn't deal with blocks (or statements) but with transactions or savepoints. The user doesn't have any other options. I still believe that to be true.

    #2
    Next I replied at 4:09pm to these comments by Stew
    >
    If a statement fails, Oracle will roll back that statement before returning control.

    If a PL/SQL block fails, Oracle will roll back the block before returning control. Think of it as an implicit savepoint at the beginning of the block, and an automatic "rollback to savepoint" if the block fails.
    >
    I responded with
    >
    Incorrect - there is NO implicit savepoint at the beginning of a block.

    As I said in my own reply to OP above you don't rollback 'blocks' - you rollback transactions. You either rollback the entire transaction or you rollback to a savepoint.
    . . . more of the same
    >
    I missed seeing that Stew was focused on what Oracle was doing so my reply was commenting on what the USER can do. But there is no need that I am aware of for an implicit savepoint at the start of each block since if an implicit rollback occurs it will be a rollback of the entire call stack to that point; not just the block in which the exception occured.

    So to state my current view on the RAISE/ROLLBACK items:

    1.RAISE does not directly cause ANYTHING to get rolled back. An implicit rollback (i.e. where the user does not directly issue a rollback) occurs at the highest level in the call stack where that particular exception is not handled.

    2. The term block-level rollback would only be applicable to the highest-level block in the call stack that the exception occured in. That is, the rollback is not strictly for the block in which the exception occured but is for that blocks parent, grandparent, etc and includes all blocks in the calling chain up to the level at which that particular exception is left unhandled. Which basically means the client level, since, as others have shown, if you swallow the exception no rollback will occur.

    3. A statement-level rollback is ONLY as described in this example by Stew from 3;54am
    >
    SQL> begin
      insert into t values(1);
      insert into t values(1);
    exception when others then null;
    end;
    / 
    anonymous block completed
     
    SQL> select * from t;
     
             N
    ----------
             1
    See, the prior statement was not rolled back because I told the P/SQL engine to ignore the exception.
    >
    And so that statement-level rollback ONLY occurs if the exception is swallowed in the same block that it occured in.

    4. I disagree with these statements by Stew in his reply of 3:58pm
    >
    If a statement fails, Oracle will roll back that statement before returning control.

    If a PL/SQL block fails, Oracle will roll back the block before returning control. Think of it as an implicit savepoint at the beginning of the block, and an automatic "rollback to savepoint" if the block fails.
    >
    Re the statement failure: The statement alone will only get rolled back if the exception it caused is swallowed in the local handler or by one further up the chain. If the exception is never swallowed then the entire calling stack will get rolled back, not just that statement.

    Except for the outermost block that an outside agency (a client) sees a block, per se, doesn't fail. It is a single statement somewhere within the block or a child block that fails. As discussed above if a statement fails and is not swallowed then the entire chain of blocks in the tree gets rolled back; not just the block that the exception directly occurred in.

    5. I disagree with these Stew statements from 4:03 also for the reasons just stated.
    >
    I showed that an exception causes a rollback of the PL/SQL block, not just the failed statement.

    This is automatic. Oracle does this for us. When we let the exception go, the calling program not only knows there was a problem, but also knows that the entire PL/SQL block was rolled back.
    >
    Those statements are confusing and I disagree with them because they seem to refer to PL/SQL blocks in general when at most they would only apply to the outermost block. An exception in an inner block never causes a rollback of just that inner block. There is either a statement-level rollback within the inner block if the exception is swallowed or there is a rollback of the entire chain of blocks in the calling tree.

    Stew doesn't explicit state that he only means the outermost block but his statement about 'the calling program . . . but also know the entire PL/SQL block was rolled back' seems ti imply that he is only talking about the outermost block.

    6.Oracle doesn't handle sequential blocks automatically with savepoints. Stew replied at 4:05pm to these comments of mine
    >
    rp0428 wrote:
    If you have multiple blocks that should be handled differently you can use a SAVEPOINT after the first block and then if the second block causes an exception you can either rollback to the SAVEPOINT (which saves the work the first block did) or rollback the entire transaction
    >
    I was described what the USER can do to process a sequential series of blocks properly. Stew replied
    >
    My point was that PL/SQL does that automatically. We don't have to.

    That is what I demonstrated.
    >
    I don't agree with that. Oracle will not handle the sequential series of blocks properly with savepoints. If you have an exception in the 2nd of three blocks you MUST swallow that exception or the exception will propagate to the caller and the 3rd block will never execute.

    And if that exception is never swallowed then the first block (all previous blocks to the one the exception occured in) will get rolled back.

    So my example demonstrated that but I don't think Stew's did.

    7. The comments by Stew and Tom Kyte on the atomicity applying to anonymous blocks are somewhat misleading. In a post at 11:51pm Stew quoted from an article by Tom Kyte
    >
    Transactions are atomic in Oracle Database, meaning that either all the statements that constitute the transaction are committed (made permanent) or all of them are rolled back ... This atomicity extends to anonymous blocks as well.
    >
    Stew's own earlier example also shows this. That example (from 3:54am) was an anonymous block with two insert statements.
    SQL> begin
      insert into t values(1);
      insert into t values(1);
    exception when others then null;
    end;
    / 
    anonymous block completed
     
    SQL> select * from t;
     
             N
    ----------
             1
    
    See, the prior statement was not rolled back because I told the P/SQL engine to ignore the exception.
    That clearly shows one statement succeeding and the other one being rolled back. The reason atomicity is maintained is because the second statement, having been rolled back is effectively removed from the transaction. The defintion of atomicity was
    >
    Transactions are atomic in Oracle Database, meaning that either all the statements that constitute the transaction are committed (made permanent) or all of them are rolled back
    >
    The reason this can confuse people (and the reason that WHEN OTHERS, when misused, is dangerous) is that yes, all statements in the transaction can be committed but the transaction no longer contains all of the statements that actually existed in the block or procedure. So technically, there is atomicity but functionally there isn't.

    That is a VERY IMPORTANT distinction and is what Stew, and others were pointing out.

    8. These comments from the same article on the atomicity applying to store-procedures are also very misleading for the same reasons just cited.
    >
    Oracle Database treated the stored procedure call as an atomic statement. The client submitted a block of code—BEGIN P; END;—and Oracle Database wrapped a SAVEPOINT around it. Because P failed, Oracle Database restored the database back to the point right before it was called.
    >
    In that example the exception was never handled so, as I have discussed above the entire calling chain of blocks is rolled back. That example only had one block.

    But consider this example where we put that code into a procedure
    create or replace procedure p1 as
      begin
        insert into t values(3);
        insert into t values('x');
      end;
      /
    Now let's call that procedure and see what result we get
    begin
      insert into t values (4);
      p1;
    exception
      when others then
        null;
    end;
    
    SQL> select * from t;
    
             N
    ----------
             4
             3
    The first INSERT in the procedure was successful even though the second raised an exception.

    Atomicity? Technically yes, functionally no. That distinction between the technical and functional is what I think causes a lot of problems for people.

    If you just blindly read that Tom Kyte article you could easily think that swallowing things is ok even though he repeatedly says that the code is just an example to show what NOT to do.

    9. Finally these comments by Stew in the last two replies
    >
    RAISE does not cause a transaction-level rollback.

    It does cause a statement-level rollback.
    >
    Disagree - raise just propagates things up the chain. It it when the exception is NEVER handled that the causing statement and ALL PREVIOUS WORK IN THE CALLING CHAIN is rolled back.
    >
    Well, my second test case doesn't even have an "original exception" - but so what?
    >
    When you use 'RAISE myException' then that IS the 'original exception' so your test case does have one.
    >
    The important thing is to agree that untrapped exceptions cause statement level rollbacks
    >
    I can't agree to that - as discussed above an untrapped exception causes the entire calling chain to be rolled back; not just the statement causing the exception.

    Here is an interesting example that shows yet another undiscussed anomaly.
    SQL> begin
      2    begin
      3      insert into t values (1);
      4      begin
      5        insert into t values(2);
      6        raise no_data_found;
      7      end;
      8    exception
      9      when others then
     10        raise ZERO_DIVIDE;
     11      end;
     12  exception
     13    when no_data_found then
     14  --  when zero_divide then
     15      null;
     16  end;
     17  /
    begin
    *
    ERROR at line 1:
    ORA-01476: divisor is equal to zero
    ORA-06512: at line 10
    ORA-01403: no data found
    
    
    SQL> select * from t;
    
    no rows selected
    Both inserts are rolled back because the NO_DATA_FOUND exception was never handled.
    The exception handler where the NO_DATA_FOUND exception occurs has a WHEN OTHERS but that block actually raises a ZERO_DIVIDE exception and does nothing with the original exception.

    Most people would think that this WHEN OTHERS has 'swallowed' the original exception but you can see by the output that in one sense it has but in another sense it hasn't.

    The outer block exception handler traps the NO_DATA_FOUND exception and does a NULL which you would think disposes of it for good.

    The output shows the ZERO_DIVIDE, becasue it was raised but never handled. But it also shows the nested NO_DATA_FOUND which you might have thought got 'swallowed' by the inner exception block.

    I'm not even sure how to turn that into an interview question but I bet it would stump a lot of people. :D
  • 25. Re: WHEN  OTHERS is a bug
    user346369 Expert
    Currently Being Moderated
    rp0428 wrote:
    If you design your architecture and code based on where you think it is going be called then IMO your approach it flawed to begin with.
    Good grief RP, you draw strange conclusions. All the code I write, and I would think practically everyone else writes, is code that needs to be called somewhere. Do you write code for no good reason??? I write code that is first requested, specified and documented, before I even start one line of PL/SQL.
    That kind of implementation isn't scalable or portable.
    How do you draw that conclusion?
    >
    When I write a package of processes that is designed to be called by non-database, and sometimes non-Oracle software
    . . .
    If the process is designed to be run by a SQL Plus process
    >
    So the code you write and run in SQL Plus isn't appropriate to be run by other non-database software. Even though the code itself may be perfectedly suited for its purpose you have to refactor the exception handling to run it by some other process.
    I work in a real-world development group. We develop reusable code wherever it is possible. But the truth of the matter is, when we write code for a specific job or task, the reality is that 99% of the time, it will not ever be used by any other process.
    That is the exact opposite of good software design.
    Just love the way you are so quick to blast away at environments you cannot relate to.

    There are two areas here. In the area of reusable code, you can be sure the code is written so that it runs perfectly when called by various "external processes". In those blocks of code, no exceptions are handled EXCEPT the ones specifically required. Database triggers are a good example. If one of those encounters an unexpected error, the default exception handling provided and raised by Oracle works quite well.

    But I spend 90% of my development time building those single-use top-level aka client-side applications. Tom Kyte's rant, and your thought processes, are directed toward those lower-level pieces that are reusable by any number of client-side or higher-level applications. (And I agree with TK's statements, and maybe would be even more strict.) However, when I build a user-side process, I NEED to report errors to the user that are more explanatory , more helpful, and more meaningful to the end-user. So just barfing out an Oracle exception error message is NOT acceptable. It is in these situations where a return code may be ok, but a return message complete with user-friendly explanatory text is best.

    Tom Kyte stated:
    The first reason is that it is far too easy for someone who invokes this procedure to ignore the return code.
    My client-side process wouldn't pass the first quality assurance test if it ignored return codes and error text messages. On top of that, any return code or message would require the top-level process to execute a Rollback.
    It's hard to believe that is really what you do or intended to say. Please provide an example of that and explain why you think the same code base should be duplicated with different exception handling if it will be called by two different external processes.
    The handling of exceptions and errors is sometimes 90% of the work required to write a high-quality end-user process. Oracle's default exception handling and error messages might be good enough for those reusable low-level internal pieces of code. But they are only a part of the entire spectrum of tools required to create an end-user application.
  • 26. Re: WHEN  OTHERS is a bug
    rp0428 Guru
    Currently Being Moderated
    >
    All the code I write, and I would think practically everyone else writes, is code that needs to be called somewhere.
    >
    Certainly it will be called from somewhere. But you don't design and incorporate dependencies into the code that make tie it to one particular caller at the expense of another.

    You said that you code one way for one caller
    >
    When I write a package of processes that is designed to be called by non-database, and sometimes non-Oracle software . . .
    >
    And another way for a different caller
    >
    If the process is designed to be run by a SQL Plus process,
    >
    That is what I was referring to. That appears to say that the first code which is 'designed to be called by non-database...' is designed differently if it is 'to be run by a SQL Plus process. That would be two sets of code where the business rule implementation is the same but the exception handling is different.

    So what I said was that was a flawed approach to design. The goal should be to avoid duplicating the business logic because that makes maintenance much more difficult and risks introducing bugs when the logic changes and only one set of code gets updated. The problems escalate if you then develop a third set of identical code with yet different exception handling.

    If different callers need to present exceptions in different or customized ways then a wrapper should be created around the business-logic layer to do that. But the business logic layer should only have ONE interface and should return exception information in one standard way.
    >
    That kind of implementation isn't scalable or portable.
    How do you draw that conclusion?
    >
    I draw that conclusion like I, hopefully, just explained above. If customized exception logic is built into and combined with the business-logic layer then the business-logic code itself is not reusable. If every caller needs unique exception handling the business-logic code itself gets duplicated; that isn't scalable.
    >
    I work in a real-world development group. We develop reusable code wherever it is possible.
    >
    So do I. And it is possible to develop reusable code for the use case we have been talking about simply by creating one component that contains the business-logic and Oracle's standard exception mechanism (error code, message, stacktrace) and then creating a customized wrapper for each caller that needs an exception 'translator' mechanism.
    >
    But the truth of the matter is, when we write code for a specific job or task, the reality is that 99% of the time, it will not ever be used by any other process.
    >
    And my 30+ years of experience has taught me to NEVER make an assumption like that. I agree with what you just stated above: develop reusable code wherever it is possible. I'm just saying that applies to the use case in this thread as well.
    >
    Just love the way you are so quick to blast away at environments you cannot relate to.
    >
    There you go making more assumptions about things you cannot possibly know anything about. That type of unsupported, and unsupportable, comment is what I am 'blasting away' at. Comments like that are not only wrong they are totally unnecessary.
    >
    But I spend 90% of my development time building those single-use top-level aka client-side applications. Tom Kyte's rant, and your thought processes, are directed toward those lower-level pieces that are reusable by any number of client-side or higher-level applications. (And I agree with TK's statements, and maybe would be even more strict.) However, when I build a user-side process, I NEED to report errors to the user that are more explanatory , more helpful, and more meaningful to the end-user. So just barfing out an Oracle exception error message is NOT acceptable. It is in these situations where a return code may be ok, but a return message complete with user-friendly explanatory text is best.
    >
    I totally agree with this with one addition. I also direct my attention to those higher-level processes. But I have just learned to keep the two as totally separate as possible because it makes things more scalable and portable and more object-oriented. I agree that the user interface for the exception/error presentation needs to be different for business/application users. I'm just saying that the actual interface for that aspect of things needs to be abstracted out so that both exception/error interfaces are still available.

    The technical people need the Oracle-specific information; the business people need the user-friendly information. A wrapper handles that requirement very nicely.
    >
    The handling of exceptions and errors is sometimes 90% of the work required to write a high-quality end-user process. Oracle's default exception handling and error messages might be good enough for those reusable low-level internal pieces of code. But they are only a part of the entire spectrum of tools required to create an end-user application.
    >
    I totally agree. All I'm saying is the best approach is a symbiotic one where each component is separate but they work together as opposed to an intertwined one where you can't work with either component without getting stuck with the specific implementation of the other one.

    I'd have to suspect that we are in greater agreement on some of these things than it might appear from a simple sentence-by-sentence reading of the thread. I know that for me it can be hard sometimes to know just what someone means by a phrase or term when you don't have that visual or audible feedback that you get in other communication mediums. So I appreciate you trying to understand what I'm really trying to say even if I don't always find the best way to say it.
  • 27. Re: WHEN  OTHERS is a bug
    Rahul_India Journeyer
    Currently Being Moderated
    I am all confused now.
  • 28. Re: WHEN  OTHERS is a bug
    BluShadow Guru Moderator
    Currently Being Moderated
    Rahul_India wrote:
    I am all confused now.
    Ok, let's make it simple... in terms of your original question...

    Don't use WHEN OTHERS without good reason... and the only good reason would be to log all errors.
    If you do use WHEN OTHERS, ensure you include a RAISE at the end of it.
  • 29. Re: WHEN  OTHERS is a bug
    APC Oracle ACE
    Currently Being Moderated
    Rahul_India wrote:
    I am all confused now.
    Your original question asked for a proof of an opinion , albeit a respected opnion from a highly-esteemed practitioner in the Oracle community. Consequently what you got was a slew of interpretation.

    Here is the question you should have asked:

    Q. Why does Tom Kyte say WHEN OTHERS THEN is a bug?

    A. Because he thinks most people don't understand how to use it properly. Other code constructs in this category include Triggers, SELECT * FROM, and (probably) [url http://radiofreetooting.blogspot.co.uk/2012/12/gotos-considered.html]GOTOs.

    The fact that you got a lot of contradictory and confusing answers proves that Tom's assertion is correct. Anything which can generate this much debate should only be used with extreme caution. Blu's [url https://forums.oracle.com/forums/thread.jspa?threadID=2470660&tstart=0&messageID=10723792#10723792]summation of the use case  is bang on.

    Cheers, APC

Legend

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