1 2 Previous Next 19 Replies Latest reply: Jun 13, 2013 5:31 AM by Billy~Verreynne RSS

    WHEN OTHERS

    Rahul_India

      Hey all,

       

      My first question on this new designed forum .I am very confused if we should use WHEN OTHERS or not.Tom kyte in one of his article wrote that 90% of bugs won't come if we stopped using

      triggers,autonomous transaction and WHEN OTHERS .I found the use of WHEN OTHERS in the oracle docs.

      So i want to ask when the use of WHEN OTHERS is justified.

       

       

      -- including OR REPLACE is more convenient when updating a subprogram
      CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS
        commission REAL;
        comm_missing EXCEPTION;
      BEGIN -- executable part starts here
        SELECT commission_pct / 100 INTO commission FROM employees
        WHERE employee_id = emp_id;
        IF commission IS NULL THEN
        RAISE comm_missing;
        ELSE
        UPDATE employees SET salary = salary + bonus*commission 
        WHERE employee_id = emp_id;
        END IF;
      EXCEPTION -- exception-handling part starts here
        WHEN comm_missing THEN
        DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');
        commission := 0;
        WHEN OTHERS THEN
        NULL; -- for other exceptions do nothing
      END award_bonus;
      /
      CALL award_bonus(150, 400);
      

       

      Is the use of WHEN OTHERS justified?

        • 1. Re: WHEN OTHERS
          newenrba

          What can i tell you is from my personal experience.
          You should strongly avoid using when OTHERS but in some some cases its needed. For example we have one process that runs for 6-8 hours. (in basic its loop of 500000 customers calculation). We cant afford to rollback this action or stop executing it. From time to time some unpredictable exceptions occur of one of calculations in this case we catch in in OTHERS and we log it. For example:

           

           

          BEGIN
          FOR I IN 1..500000
          LOOP
          PROCEDURE_CAL1(I,PA1,PA2....0);
          END LOOP;
          END;
          
          PROCEDURE_CAL1(I,PA1,PA2....0) AS
          BEGIN
          SOME CALCULATIONS ;
          EXCEPTIONS
          --all expected exceptions 
          WHEN ... THEN
          ....
          WHEN ...THEN
          --sometimes this happend
          
          WHEN OTHERS THEN
          INSERT INTO LOG_TABLE VALUES (.,,,,,,,sqlerrm,,,,);
          END;
          

           

          And again you should avoid OTHERS

          • 2. Re: WHEN OTHERS
            sybrand_b

            The use of WHEN OTHERS in this code is definitely NOT justified as it will suppress any technical (space-related) issue during UPDATE,

            and it will suppress the NO_DATA_FOUND exception when you specify a wrong employee id.

            Also you can and you should be doing this in ONE statement, the SELECT is redundant.

             

            ---------

            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: WHEN OTHERS
              Billy~Verreynne

              The WHEN OTHERS exception is not an invalid exception. It depends on how it is used.

               

              Your code is not threadsafe. In between the SELECT and the UPDATE, the row data selected for using in the update can change. The truth at the time of the SELECT may no longer be true at the time of the UPDATE. Is this what you want?

               

              Why raise an invalid commission exception when there is nothing done in the exception handler with that exception? Setting commission to zero in the exception handler? Why? What for? Who for? That variable goes out of scope with the next statement and ceases to exist. Why set it?

               

              As Sybrand Bakker said - use a single UPDATE statement to make the change. If zero rows are updated and this is not expected, raise an exception. And within this context, there is no use for a WHEN OTHERS exception at all.

              • 4. Re: WHEN OTHERS
                Rahul_India
                
                

                sybrand_b wrote:

                 

                The use of WHEN OTHERS in this code is definitely NOT justified as it will suppress any technical (space-related) issue during UPDATE,

                and it will suppress the NO_DATA_FOUND exception when you specify a wrong employee id.

                Also you can and you should be doing this in ONE statement, the SELECT is redundant.

                 

                ---------

                Sybrand Bakker

                Senior Oracle DBA

                 

                Hi Sybrand ,

                 

                Suppose if we pass no employee id .then no value will be inserted into  commission variable.

                 

                This error would be handled by the when other since it is the super set

                WHEN OTHERS THEN
                INSERT INTO LOG VALUES (SQLERR,..);
                
                

                 

                We can catch the When no data error in this way to by using When others .

                • 5. Re: WHEN OTHERS
                  Rahul_India

                  BillyVerreynne wrote:

                   

                  The WHEN OTHERS exception is not an invalid exception. It depends on how it is used.

                   

                  Your code is not threadsafe. In between the SELECT and the UPDATE, the row data selected for using in the update can change. The truth at the time of the SELECT may no longer be true at the time of the UPDATE. Is this what you want?

                   

                  Why raise an invalid commission exception when there is nothing done in the exception handler with that exception? Setting commission to zero in the exception handler? Why? What for? Who for? That variable goes out of scope with the next statement and ceases to exist. Why set it?

                   

                  As Sybrand Bakker said - use a single UPDATE statement to make the change. If zero rows are updated and this is not expected, raise an exception. And within this context, there is no use for a WHEN OTHERS exception at all.

                   

                   

                  i took this code from ORACLE DOCS

                  • 6. Re: WHEN OTHERS
                    Rahul_India

                    BillyVerreynne wrote:

                     

                    The WHEN OTHERS exception is not an invalid exception. It depends on how it is used.

                     

                    Your code is not threadsafe. In between the SELECT and the UPDATE, the row data selected for using in the update can change. The truth at the time of the SELECT may no longer be true at the time of the UPDATE. Is this what you want?

                     

                    Why raise an invalid commission exception when there is nothing done in the exception handler with that exception? Setting commission to zero in the exception handler? Why? What for? Who for? That variable goes out of scope with the next statement and ceases to exist. Why set it?

                     

                     

                     

                    I can i make the above code thread safe/

                    • 7. Re: WHEN OTHERS
                      Billy~Verreynne

                      The sample code in Oracle documentation is unfortunately of a poor standard - it demonstrates basic technical concepts without any consideration wrt how that concept is applied in real-world code.

                      • 8. Re: WHEN OTHERS
                        Billy~Verreynne

                        A safe procedure (that does not use potentially different versions of the same data), will look something as follows:

                         

                        create or replace procedure AwardBonus  (empID employees.emp_id%Type, empBonus number ) is
                        begin
                          update employees
                          set salary = salary + (empBonus * commission_pct  / 100)
                          where emp_id = empID;
                        
                          if SQL%RowCount = 0 then
                            --// no row updated
                            raise_application_error(
                              -20001,
                             'Bonus not applied as employee ['||empID||'] does not exist'
                            );
                          end if;
                        
                        end;
                        
                        • 9. Re: WHEN OTHERS
                          Sandeep M.

                          Hi,

                           

                          Use below statement, instead of select and update:

                           

                          UPDATE employees e1 SET e1.salary = e1.salary + e1.bonus*(e1.commission_pct/100)     

                          WHERE e1.employee_id = emp_id

                          AND EXISTS (SELECT 1 FROM employees e2

                                               WHERE  e1.employee_id = e2.employee_id

                                               AND    e2.commission_pct IS NOT NULL)

                          • 10. Re: WHEN OTHERS
                            Rahul_India

                            So only WHEN OTHERS THEN NULL is a bug. If i use this piece of code

                             

                            When others then
                            dbms_output.put_line(sqlerrm);
                            
                            
                            

                             

                            Is above code fine?

                            • 11. Re: WHEN OTHERS
                              dariyoosh

                              Hi,

                               

                              Just for information, here is an interesting article about WHEN OTHERS.

                               

                              http://www.orafaq.com/wiki/WHEN_OTHERS

                               

                              Regards,

                              Dariyoosh

                              • 12. Re: WHEN OTHERS
                                Billy~Verreynne
                                1. When others then 
                                2. dbms_output.put_line(sqlerrm); 
                                Is above code fine?

                                 

                                No. That code is especially wrong. Which makes that exception handler a nasty bug.

                                • 13. Re: WHEN OTHERS
                                  Rahul_India

                                  Well this is the part that i need to understand. My assumption :- I am catching all the errors possible using when others and displaying what error it is using SQLERRM.

                                  • 14. Re: WHEN OTHERS
                                    Billy~Verreynne

                                    The displaying is done by writing error as a text string into a variable that resides as a static array in the DBMS_OUTPUT package.

                                     

                                    So if I call your code, and there is an exception

                                    • how do I know that your code put the error as text into a variable of some other package?
                                    • even if I did know, how do I recognise the error you wrote to it from other text (e.g. debug data) that has also been written to the variable?

                                     

                                    PL/SQL (like Java, C# and many other languages) use exceptions as an integral feature of the language. There is a language contract between your code and my code.

                                     

                                    That contract says that if there is an error, your code will tell me via an exception. If you code a WHEN OTHERS exception, you are saying you are handling the exception - fixing the error. But your code does not fix the error. It suppresses the error. And write it as a text string to some variable sitting somewhere else.

                                     

                                    My code does not get an exception. And this, according to the contract we have, says that your code was successful and there were no errors. But this is not the case. Your code is lying. Your code has broken the contract.

                                     

                                    So when do you write an exception handler? There are 3 basic reasons:

                                     

                                    1. The error is not an error and your code can fix it. For example, your code can treat a NO_DATA_FOUND as a valid and expected event and not an error.

                                    2. The exception handler is used as a resource protection block. You need to catch the exception to prevent your code's resources from leaking - and after you have cleaned up the resources, you need to re-raise the exception so that it can continue to travel up the call stack.

                                    3. The exception is generic and your code wants to  make it an application specific and meaningful exception. For example, turning a NO_DATA_FOUND exception into an application
                                      NO_CUSTOMER_FOUND exception.

                                    Suppressing an exception, when it is not "fixed", is a bug.

                                     

                                    Changing an exception into a text string variable (error message) or integer variable (error code) is flawed.

                                    1 2 Previous Next