3 Replies Latest reply: Feb 25, 2014 5:16 AM by John Stegeman RSS

Exception in Declaration block

864019 Newbie
Currently Being Moderated

Hi All,

 

When an exception is raised in declaration section why it propagates to enclosing block? Why not to same block?

 

Some one please tell me.

 

Thanks,

maddy

  • 1. Re: Exception in Declaration block
    Nimish Garg Guru
    Currently Being Moderated

    Can you please explain with an example ???

  • 2. Re: Exception in Declaration block
    Karthick_Arp Guru
    Currently Being Moderated

    That's how exception handling is designed in oracle. Here is a simple example.

    SQL> create or replace procedure p
      2  as
      3    v integer := 'a';
      4  begin
      5    null;
      6  exception
      7    when others then
      8      null;
      9  end;
    10  /

     

    Procedure created.

     

    SQL> begin
      2    p;
      3  end;
      4  /
    begin
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "ARBORU.P", line 3
    ORA-06512: at line 2


    SQL> begin
      2    p;
      3  exception
      4    when others then
      5      null;
      6  end;
      7  /

     

    PL/SQL procedure successfully completed.

     

    SQL>

     

    Following thread PL/SQL 101 : Exception Handling could be a good read.

  • 3. Re: Exception in Declaration block
    John Stegeman Oracle ACE
    Currently Being Moderated

    Do you mean this:

     

      1  declare                                                                   

      2  x number := 1/0;                                                          

      3  begin                                                                     

      4  begin                                                                     

      5  null;                                                                     

      6  exception                                                                 

      7  when others then dbms_output.put_line('never have an exception handler like

    this');                                                                       

      8  end;                                                                      

      9* end;                                                                      

    SQL> /                                                                         

    declare                                                                        

    *                                                                              

    ERROR at line 1:                                                               

    ORA-01476: divisor is equal to zero                                            

    ORA-06512: at line 2         

     

    The exception handler isn't in scope                                                

Legend

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