14 Replies Latest reply: Jul 10, 2013 6:52 AM by S10390 RSS

    exceptions

    Saahithi

      Hi Masters,

       


      I have attended an interview last week. They asked questions on exceptions. I didn't gave proper answers because of little bit confuse. Ofcourse these are basic questions.


      questions


      1. I have pl/sql block with declare, begin, exception and end parts. If I give Raise keyword in begin / exception part what will happens?


      2. In the begin section I have called user defined exception, then it will go and execute the statements in the exception part. again pointer will come back to main block(begin part) or not ? If I have some more dml statements are there in the main block those will execute or not?


      3. Are there any rules to write exceptions in a proper order ?


      4. If I give when others then exception in the first place what will happens? why we have to write when others then exception in the last place?


      Please advise...!!


      Regards

      AR

        • 1. Re: exceptions
          Alex Nuijten

          All these questions and more are answered in the docs: PL/SQL Error Handling

          • 2. Re: exceptions
            Frank Kulash

            Hi.

             

            1.  If you RAISE an exception in the BEGIN section (that is, after the keyword BEGIN, but before its matching EXCEPTION keyword), execution of the BEGIN section stops, and control passes to the EXCEPTION section (if there is one).

            If you raise an EXCEPTION in the EXCEPTION section, then control passes back to the calling block.

             

            2. After you leave a BEGIN section to handle an exception, control does not come back.  Use nested blocks if you want to resume at the point right after the exception was rraised.

             

            3. What is "proper order"?

            If the WHEN clauses handle mutually exclusive conditions, then the order doesn't change the results.

             

            4. WHEN OTHERS includes all cases.  If you have something like WHEN NO_DATA_FOUND after WHEN OTHERS, the WHEN NO_DATA_FOUND will never get executed.

            • 3. Re: exceptions
              myOra_help

              1. I have pl/sql block with declare, begin, exception and end parts. If I give Raise keyword in begin / exception part what will happens?

               

              Ans. You can't use RAISE (without any exception name) in execution part, you can use RAISE without any exception name in exception section to re-raise the raised exception.

               

               

              2. In the begin section I have called user defined exception, then it will go and execute the statements in the exception part. again pointer will come back to main block(begin part) or not ? If I have some more dml statements are there in the main block those will execute or not?

               

              Ans. Once control left execution part by raising exception then it won't come to the same execution part. You can use below syntax to handle such situation.

               

              begin

              ......

              begin

                 statements..

              exception handlers

              end;

              ...

              other statements

              ...

              [exception handler]

              end;


              3. Are there any rules to write exceptions in a proper order ?

               

              Ans. Not exactly, Only one exception is raised at a time and a perticular exception handler if any presents handles it. You can raise other exceptions if you want on that handler. The only thing you must know that OTHERS can only comes to the last exception handler OR you can get the compile time error.


              4. If I give when others then exception in the first place what will happens? why we have to write when others then exception in the last place?

               

              Ans. OTHERS can only comes to the last exception handler OR you can get the compile time error. Because OTHERS can handles any exception including user defined exceptions you should declare it to the last.

               

               

               

              • 4. Re: exceptions
                Rahul_India

                 


                1. I have pl/sql block with declare, begin, exception and end parts. If I give Raise keyword in begin / exception part what will happens?


                2. In the begin section I have called user defined exception, then it will go and execute the statements in the exception part. again pointer will come back to main block(begin part) or not ? If I have some more dml statements are there in the main block those will execute or not?


                3. Are there any rules to write exceptions in a proper order ?


                4. If I give when others then exception in the first place what will happens? why we have to write when others then exception in the last place?


                 

                1. RAISE will pass the control to the caller of the procedure.

                2.NO.NO

                3.YES

                4.It is simple question.If you can't answer this then you need to brush up your PL/SQL .

                • 5. Re: exceptions
                  Rahul_India

                  Hi Frank you can't include any exception after WHEN OTHERS (if i am not wrong).

                   

                  create or replace procedure t_test 
                  is
                   begin
                  
                   raise value_error;
                  
                   dbms_output.put_line ('1');
                  
                   exception
                   when others then 
                    dbms_output.put_line ('2');
                    when no_data_found then 
                    dbms_output.put_line ('3');
                    end;
                  
                    show error
                  
                  PROCEDURE t_test compiled
                  Warning: execution completed with warning
                  10/2           PLS-00370: OTHERS handler must be last among the exception handlers of a block
                  0/0            PL/SQL: Compilation unit analysis terminated
                  
                  • 6. Re: exceptions
                    BluShadow

                    Rahul_India wrote:

                     

                    Hi Frank you can't include any exception after WHEN OTHERS (if i am not wrong).

                     

                    1. create or replace procedure t_test  
                    2. is 
                    3. begin 
                    4. raise value_error; 
                    5. dbms_output.put_line ('1'); 
                    6. exception 
                    7. when others then  
                    8.   dbms_output.put_line ('2'); 
                    9.   when no_data_found then  
                    10.   dbms_output.put_line ('3'); 
                    11.   end
                    12.   show error 
                    13. PROCEDURE t_test compiled 
                    14. Warning: execution completed with warning 
                    15. 10/2           PLS-00370: OTHERS handler must be last among the exception handlers of a block 
                    16. 0/0            PL/SQL: Compilation unit analysis terminated 

                     

                    Yes, I'm sure Frank knows that.  He was explaining the reason WHY a WHEN OTHERS has to come last, not just the fact that it would generate a compiler error.

                    • 7. Re: exceptions
                      Frank Kulash

                      Hi,

                       

                      BluShadow wrote:

                       

                       

                       

                      ... Yes, I'm sure Frank knows that.  He was explaining the reason WHY a WHEN OTHERS has to come last, not just the fact that it would generate a compiler error.

                      Good point.  It would have been better if I had phrased it as:

                       

                      "If you could have something like WHEN NO_DATA_FOUND after WHEN OTHERS, the WHEN NO_DATA_FOUND would never get executed."

                      • 8. Re: exceptions
                        Saahithi

                        Hi Rahul,

                         

                        You are saying that we have a particular order to write an exceptions? that is that? Can you please explain me.

                         

                        Note : somebody is telling that When Others then exception behaviors  is different in 10g and 11g. In 11g you can write in the first place in the exception part. Is it correct? I am searching . but I didn't find any where .

                         

                        Please advise...!!!

                        • 9. Re: exceptions
                          Alex Nuijten

                          In Oracle 11g you can get a warning if you have a WHEN OTHERS which doesn't include a RAISE or RAISE_APPLICATION_ERROR

                          • 10. Re: exceptions
                            Saahithi

                            Yah Alex...I got this info. when I saw the below url..Anyhow Thanks once again.

                             

                             

                            http://www.oracle.com/technetwork/articles/sql/11g-efficient-coding-093640.html

                             

                            But Rahul has given YES answer for my 3rd question. Asking what is that order sequence?

                             

                            Regards

                            AR

                            • 11. Re: exceptions
                              BluShadow

                              Saahithi wrote:

                               

                              Yah Alex...I got this info. when I saw the below url..Anyhow Thanks once again.

                               

                               

                              http://www.oracle.com/technetwork/articles/sql/11g-efficient-coding-093640.html

                               

                              But Rahul has given YES answer for my 3rd question. Asking what is that order sequence?

                               

                              Regards

                              AR

                               

                              Exception handlers are there to capture particular exceptions.  If an exception is raised in the code, it goes to the exception handler block and goes down the list of exceptions looking for an exception handler specific to the exception that has been raised.  It doesn't matter what order each exception handler is written as each one will only work for one specific exception, with the exception of the WHEN OTHERS exception which has to come last because it can handle any exception that is raised, so if you could include it before other exceptions (which you can't) then it would prevent any specific handler after it from being able to process any exception it's been written to handle.

                              • 12. Re: exceptions
                                Saahithi

                                Thanks a lot blushadow. Nice to know..so much of info. on exceptions. And thanks to everybody for your kind help.

                                 

                                Regards

                                Ar

                                • 13. Re: exceptions
                                  Rahul_India

                                  you can go throught this discussion https://forums.oracle.com/thread/2549365

                                  • 14. Re: exceptions
                                    S10390

                                    Adding to it ...

                                     

                                    One of the most informative thread from Blu's hand on Exception Handling.  PL/SQL 101 : Exception Handling