This discussion is archived
14 Replies Latest reply: Jul 10, 2013 4:52 AM by S10390 RSS

exceptions

Saahithi Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

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

  • 2. Re: exceptions
    Frank Kulash Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

     


    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 Journeyer
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

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

  • 14. Re: exceptions
    S10390 Journeyer
    Currently Being Moderated

    Adding to it ...

     

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

Legend

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