2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

PL\SQL Exceptions handling between functions

I have a simple problem here, but I spend my "recomended 30 minutes" trying to resolve this, but with no success. :)

I have a first procedure like this

procedure p1(a in varchar2)

is

begin

-- call second procedure

p2(a);

-- other things

end;


My second procedure may have an exception to raise:

procedure p2(a in varchar2)

is

lv varchar2(20);

begin

   select "1" into lv from dual;

   if lv = "2" then

          raise general_exception;

end if;

end;


My general_exception variable is a global exceptions type, inside the package where both of my procedures are in.

What I want is to catch the exception in the first procedure (p1) to avoid the execution of the other things.

I"ve tried this but with no results:

 procedure p1(a in varchar2)

    is

    begin

    -- call second procedure

    p2(a);

    -- other things

exception when general_exception then

  dbms_output.put_line("ERROR");

end;

userimage

It works for me (nb. I did change your p2 to reference the parameter being passed in, rather than the static value of '1'!):

create or replace package test_pkg

as

  general_exception exception;

  procedure p1 (p_a in varchar2);

end test_pkg;

/

create package body test_pkg

as

  procedure p2 (p_a in varchar2)

  is

  begin

    if p_a = '2' then

      raise general_exception;

    end if;

  end p2;

 

  procedure p1 (p_a in varchar2)

  is

  begin

    p2(p_a);

    dbms_output.put_line('p2 executed sucessfully. Do all of the things.');

  exception

    when general_exception then

      dbms_output.put_line('ERROR');

  end p1;

end test_pkg;

/

set serveroutput on;

 

begin

  test_pkg.p1('1');

  test_pkg.p1('2');

end;

/

And the output of running the p1 procedure with the two different values was:

p2 executed sucessfully. Do all of the things.

ERROR

which is as expected.

Answer is