5 Replies Latest reply: Aug 12, 2014 10:38 AM by theoa RSS

    Trying to handle exception from another block, Not getting it...Need valuable help please....

    Arpit Agrawal

      Hi Champs,

       

      I want to RAISE any an exception through my first procedure and want to handle it in second procedure. [I think, this is exception propagating concept].

      Not able to do so, need your valuable guidance.


      create or replace procedure test_excep1 (a in number, b in number)
      is
      c number;
      external_exception exception;
              begin
              c:=a/b;
              dbms_output.put_line('Output is' || c);
              exception
              when zero_divide then
              raise external_exception;
      end;

      begin
      test_excep(2,0);
      end;

      While executing above Procedure with divisor as zero, i get below error message

      ORA-06510: PL/SQL: unhandled user-defined exception
      ORA-06512: at "SONARDBO.TEST_EXCEP", line 10
      ORA-01476: divisor is equal to zero
      ORA-06512: at line 2


      Now if i try to catch this exception by another procedure, it doesnt help me. --  This is my exact requirement.


      create procedure test_excep2(d in number,e in number)
      is
      external_exception exception;
      begin
              test_excep(d,e);
              exception when external_exception then
              dbms_output.put_line('Exception handling Works');
      end;

      begin
      test_excep2(2,0);
      end;

       

      While executing above block, getting below message.

       

      ORA-06510: PL/SQL: unhandled user-defined exception

      ORA-06512: at "SONARDBO.TEST_EXCEP", line 10

      ORA-01476: divisor is equal to zero

      ORA-06512: at "SONARDBO.TEST_EXCEP2", line 5

      ORA-06512: at line 2

       

      Thanks a Ton in advance for help.

        • 2. Re: Trying to handle exception from another block, Not getting it...Need valuable help please....
          Billy~Verreynne

          Just because you called an undefined exception "external_exception" in one code unit, and then reuse the name in another - does not mean that these exceptions variables are for the same exception. Common names mean nothing in this case. Using the same name for variables or exceptions or constants across code units/modules, does not mean that these are now the same.

          • 3. Re: Trying to handle exception from another block, Not getting it...Need valuable help please....
            BluShadow

            As Billy indicates with his answer, the scope of the user defined exception has to exist across all units that use it.  You can't just use the same name in seperate execution blocks and expect PL/SQL to magically tie the scopes together just because they're called the same thing (that would be like expecting a variable called "X" of varchar2 type to pick up the value of an inner block's X varchar2 variable just because it's called the same name)

             

            If the exception is properly scoped it will work...

             

            SQL> ed
            Wrote file afiedt.buf

              1  declare
              2    external_exception exception;
              3    procedure test_excep(a in number, b in number) is
              4      c number;
              5    begin
              6      c:=a/b;
              7      dbms_output.put_line('Output is' || c);
              8    exception
              9      when zero_divide then
            10        raise external_exception;
            11    end;
            12  begin
            13    test_excep(2,0);
            14  exception
            15    when external_exception then
            16      dbms_output.put_line('Exception captured!');
            17* end;
            SQL> /
            Exception captured!

             

            PL/SQL procedure successfully completed.

             

             

            Also read: PL/SQL 101 : Exception Handling

            • 4. Re: Trying to handle exception from another block, Not getting it...Need valuable help please....
              John Spencer

              Another possibility if you want to use exceptions across code blocks is to create  package spec to define the exceptions, then raise the package exceptions like:

              SQL> create package exceptions_pkg as
                2    external_exception exception;
                3    pragma exception_init(external_exception, -20001);
                4  end;
                5  /

              Package created.

              SQL> create procedure test_excep(a in number,
                2                              b in number) as
                3    c number;
                4  begin
                5    c:=a/b;
                6    dbms_output.put_line('Output is' || c);
                7  exception
                8    when zero_divide then
                9        raise exceptions_pkg.external_exception;
              10  end;
              11  /

              Procedure created.

              SQL> begin
                2    test_excep(2,0);
                3  exception
                4    when exceptions_pkg.external_exception then
                5        dbms_output.put_line('Exception captured!');
                6  end;
                7  /
              Exception captured!

              PL/SQL procedure successfully completed.

              John

              • 5. Re: Trying to handle exception from another block, Not getting it...Need valuable help please....
                theoa

                And yet another way is to bind the exception in both cases to the same error number.

                Although I would prefer either of the solutions above, over this one.

                 

                create procedure test_excep

                  ( a in number

                  , b in number

                  )

                as

                  external_exception exception;

                  pragma exception_init(external_exception, -20001);

                  c number;

                begin

                  c:=a/b;

                  dbms_output.put_line('Output is' || c);

                exception

                  when zero_divide then

                    raise external_exception;

                end;

                /

                 

                declare

                  external_exception exception;

                  pragma exception_init(external_exception, -20001);

                begin

                  test_excep(2,0);

                exception

                  when external_exception then

                    dbms_output.put_line('Exception captured!');

                end;

                /