1 2 3 4 Previous Next 49 Replies Latest reply: Jan 11, 2013 12:07 AM by Billy~Verreynne RSS

    WHEN  OTHERS is a bug

    Rahul_India
      Good evening everyone,

      I was just reading an article by TOM KYTE where he mentioned this very statement

      *"A when others is almost always a BUG unless it is immediately followed by a RAISE.I truly wish we didn't even support WHEN OTHERS."*



      Can anyone give me a simple example to prove that above statement is true.I know its true ...lol
        • 1. Re: WHEN  OTHERS is a bug
          Hoek
          Well, he blogged about it as well, with enough examples, like:
          http://tkyte.blogspot.nl/2008/01/why-do-people-do-this.html
          http://tkyte.blogspot.nl/2007/03/dreaded-others-then-null-strikes-again.html
          etc.
          or just search asktom on "i hate your code" ;)
          • 2. Re: WHEN  OTHERS is a bug
            BluShadow
            Rahul_India wrote:
            Good evening everyone,

            I was just reading an article by TOM KYTE where he mentioned this very statement

            A when others is almost always a BUG unless it is immediately followed by a RAISE.I truly wish we didn't even support WHEN OTHERS.



            Can anyone give me a simple example to prove that above statement is true.I know its true ...lol
            The point of exception handling is that you are going to handle exceptions that you expect to happen. A WHEN OTHERS is saying you want to catch when you get an exception you're not expecting... but in truth, if you're getting an exception you're not expecting then by it's very nature you're not going to know how to handle it (otherwise you would have written an exception handler specifically for that exception). The only thing WHEN OTHERS should be used for is if there is a requirement to log errors, and even then the error should still be raised up as Tom says, in case any calling code has handlers written to handle that specific exception. That's the point of writing good exception handling... if you don't know how to handle an exception, don't try and catch it... just let it get raised.
            • 3. Re: WHEN  OTHERS is a bug
              Stew Ashton
              WHEN OTHERS not followed by RAISE changes the way the database works!

              Consider:
              SQL> create table t(n number primary key);
              
              table T created.
              
              SQL> begin
                insert into t values(1);
                insert into t values(1);
              end;
              /
              Error starting at line 5 in command:
              begin
                insert into t values(1);
                insert into t values(1);
              end;
              Error report:
              ORA-00001: unique constraint (STEW.SYS_C0034442) violated
              ORA-06512: at line 3
              00001. 00000 -  "unique constraint (%s.%s) violated"
              *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
                         For Trusted Oracle configured in DBMS MAC mode, you may see
                         this message if a duplicate entry exists at a different level.
              *Action:   Either remove the unique restriction or do not insert the key.
              
              SQL> select * from t;
              
              no rows selected
              This is the way the database is supposed to work. The entire PL/SQL block was rolled back and an exception was raised to the calling program. Now this:
              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.

              So you see, not only did I ignore the exception, but Oracle ignored the exception.

              That is why WHEN OTHERS without RAISE is doubly dangerous.
              • 4. Re: WHEN  OTHERS is a bug
                Rahul_India
                Now this:
                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.

                So you see, not only did I ignore the exception, but Oracle ignored the exception.

                That is why WHEN OTHERS without RAISE is doubly dangerous.
                Shouldn't the above code be right?
                • 5. Re: WHEN  OTHERS is a bug
                  Rahul_India
                  Hoek wrote:
                  Well, he blogged about it as well, with enough examples, like:
                  http://tkyte.blogspot.nl/2008/01/why-do-people-do-this.html
                  http://tkyte.blogspot.nl/2007/03/dreaded-others-then-null-strikes-again.html
                  etc.
                  or just search asktom on "i hate your code" ;)
                  Do you even sleep ? :p ;)
                  • 6. Re: WHEN  OTHERS is a bug
                    Paul  Horth
                    Rahul_India wrote:
                    Now this:
                    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.

                    So you see, not only did I ignore the exception, but Oracle ignored the exception.

                    That is why WHEN OTHERS without RAISE is doubly dangerous.
                    Shouldn't the above code be right?
                    No, the point is that an exception should be thrown so that the calling routine can make the decision to roll back.

                    With the above code the calling routine would think everything was OK and commit - even though only half your code work correctly.

                    OK, it's a simple example but think what would happen if the first statement was a debit from your current account
                    and the second statement was a credit to who you were paying, and that second one had an error.

                    Then you would have lost the money from your account and the person expecting the money would still be chasing you for it:
                    and the bank would claim everything was OK as it had all seemed to go correctly.
                    • 7. Re: WHEN  OTHERS is a bug
                      Rahul_India
                      Paul  Horth wrote:
                      Rahul_India wrote:
                      Now this:
                      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.

                      So you see, not only did I ignore the exception, but Oracle ignored the exception.

                      That is why WHEN OTHERS without RAISE is doubly dangerous.
                      Shouldn't the above code be right?
                      No, the point is that an exception should be thrown so that the calling routine can make the decision to roll back.

                      With the above code the calling routine would think everything was OK and commit - even though only half your code work correctly.

                      OK, it's a simple example but think what would happen if the first statement was a debit from your current account
                      and the second statement was a credit to who you were paying, and that second one had an error.

                      Then you would have lost the money from your account and the person expecting the money would still be chasing you for it:
                      and the bank would claim everything was OK as it had all seemed to go correctly.
                      Wow great explanation....so what it means that whenever we encounter an exception in the PL/SQL ..whole block should be rolled back.Right?
                      • 8. Re: WHEN  OTHERS is a bug
                        Hoek
                        Do you even sleep ? :p ;)
                        I actually do, it's one of my favourite hobbies ;)

                        Anyway, this 'classic explanational rant' (imho) deserves to be part of the discussion as well:
                        http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552
                        (you might need to wait a few seconds, until the cursor has scrolled down to the followup of March 3, 2009 - 7am Central time zone)
                        • 9. Re: WHEN  OTHERS is a bug
                          Paul  Horth
                          Rahul_India wrote:
                          Paul  Horth wrote:
                          Rahul_India wrote:
                          Now this:
                          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.

                          So you see, not only did I ignore the exception, but Oracle ignored the exception.

                          That is why WHEN OTHERS without RAISE is doubly dangerous.
                          Shouldn't the above code be right?
                          No, the point is that an exception should be thrown so that the calling routine can make the decision to roll back.

                          With the above code the calling routine would think everything was OK and commit - even though only half your code work correctly.

                          OK, it's a simple example but think what would happen if the first statement was a debit from your current account
                          and the second statement was a credit to who you were paying, and that second one had an error.

                          Then you would have lost the money from your account and the person expecting the money would still be chasing you for it:
                          and the bank would claim everything was OK as it had all seemed to go correctly.
                          Wow great explanation....so what it means that whenever we encounter an exception in the PL/SQL ..whole block should be rolled back.Right?
                          Not just that block. The whole transaction should be rolled back. After all, this particularly procedure may be the third called in a whole series
                          of procedures. The overall controlling (application-level) program should catch any error thrown by the procedures it's calling and rollback
                          the whole transaction.

                          Only if all procedures in the transaction complete successfully should a commit be done.
                          • 10. Re: WHEN  OTHERS is a bug
                            rp0428
                            >
                            so what it means that whenever we encounter an exception in the PL/SQL ..whole block should be rolled back.Right?
                            >
                            NO! You don't rollback 'blocks' - you rollback transactions. You either rollback the entire transaction or you rollback to a savepoint.

                            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.

                            See the 'Creating Savepoints: Example in the SQL Language doc
                            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10001.htm
                            Creating Savepoints: Example To update the salary for Banda and Greene in the sample table hr.employees, check that the total department salary does not exceed 314,000, then reenter the salary for Greene:
                            UPDATE employees 
                                SET salary = 7000 
                                WHERE last_name = 'Banda';
                            SAVEPOINT banda_sal;
                            
                            UPDATE employees 
                                SET salary = 12000 
                                WHERE last_name = 'Greene';
                            SAVEPOINT greene_sal;
                            
                            SELECT SUM(salary) FROM employees;
                            
                            ROLLBACK TO SAVEPOINT banda_sal;
                             
                            UPDATE employees 
                                SET salary = 11000 
                                WHERE last_name = 'Greene';
                             
                            COMMIT; 
                            • 11. Re: WHEN  OTHERS is a bug
                              user346369
                              Rahul_India wrote:
                              Good evening everyone,

                              I was just reading an article by TOM KYTE where he mentioned this very statement

                              *"A when others is almost always a BUG unless it is immediately followed by a RAISE.I truly wish we didn't even support WHEN OTHERS."*



                              Can anyone give me a simple example to prove that above statement is true.I know its true ...lol
                              When I write a package of processes that is designed to be called by non-database, and sometimes non-Oracle software (Example: Oracle Forms, or a web process), if my procedure raises an unexpected error, I will handle it like this:
                              Exception when others then
                                Rtrn_message := 'My process location: '||sqlerrm);
                                -- set flags here to stop further processing --
                              End;
                              The problem with RAISE; is that the software calling my package cannot tell where the error occurred. By passing back text in the Rtrn_Message variable, the error is at least reported, and maybe logged, so that someone can find the actual location within the package code where the problem actually occurred. Using this method, we usually return null in the Rtrn_Message if the process succeeded, and an error message if anything did not.

                              Without reporting the location, it is a nightmare trying to determine where the code failed.

                              In other cases, I have ranted against people even writing WHEN OTHERS THEN RAISE;
                              If the process is designed to be run by a SQL Plus process, doing the RAISE; causes SQL Plus to report the line number where the RAISE; occurred. Without the When others...., the correct line number -- where the actual error occurred, is reported. So locating the actual statement causing the error is simple.

                              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, because:
                                1. it is the default behavior.
                                2. it causes other Oracle software (like SQL Plus) from reporting the true problem location.
                              WHEN OTHERS THEN NULL should be forbidden!
                              • 12. Re: WHEN  OTHERS is a bug
                                rp0428
                                >
                                When I write a package of processes that is designed to be called by non-database, and sometimes non-Oracle software (Example: Oracle Forms, or a web process), if my procedure raises an unexpected error, I will handle it like this:
                                Exception when others then
                                  Rtrn_message := 'My process location: '||sqlerrm);
                                  -- set flags here to stop further processing --
                                End;
                                The problem with RAISE; is that the software calling my package cannot tell where the error occurred. By passing back text in the Rtrn_Message variable, the error is at least reported, and maybe logged, so that someone can find the actual location within the package code where the problem actually occurred. Using this method, we usually return null in the Rtrn_Message if the process succeeded, and an error message if anything did not.

                                Without reporting the location, it is a nightmare trying to determine where the code failed.

                                In other cases, I have ranted against people even writing WHEN OTHERS THEN RAISE;
                                If the process is designed to be run by a SQL Plus process, doing the RAISE; causes SQL Plus to report the line number where the RAISE; occurred. Without the When others...., the correct line number -- where the actual error occurred, is reported. So locating the actual statement causing the error is simple.
                                >
                                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.
                                That kind of implementation isn't scalable or portable.
                                >
                                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.

                                That is the exact opposite of good software design.

                                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.
                                • 13. Re: WHEN  OTHERS is a bug
                                  Stew Ashton
                                  Rahul_India wrote:
                                  Now this:
                                  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.

                                  So you see, not only did I ignore the exception, but Oracle ignored the exception.

                                  That is why WHEN OTHERS without RAISE is doubly dangerous.
                                  Shouldn't the above code be right?
                                  No. You missed my point.

                                  In your code, you can tell Oracle to commit and you can tell Oracle to rollback, but Oracle will sometimes rollback all by itself.

                                  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.

                                  WHEN OTHERS without a RAISE causes the PL/SQL block to "succeed", so the SQL statement is rolled back but not the rest of the PL/SQL block. That means you have caused the database to no longer behave the way it is meant to.
                                  • 14. Re: WHEN  OTHERS is a bug
                                    Stew Ashton
                                    Paul  Horth wrote:
                                    No, the point is that an exception should be thrown so that the calling routine can make the decision to roll back.
                                    That is true and important, but it was not the point of my demonstration.

                                    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. This is important information: the caller knows exactly what state the data is in, so he can decide to roll back, commit or do some other work.
                                    1 2 3 4 Previous Next