PL\SQL Exceptions handling between functions

Question

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.

I want 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;

so what happens? What error is raised? Show us where you declare general exception.. are P1 and P2 inside a package? — kevinskio
– kevinskio, Commented Sep 30, 2015 at 15:16
"My general_exception variable is a global exceptions type, inside the package where both of my procedures are in." — milheiros
– milheiros, Commented Sep 30, 2015 at 15:19
When I say "with no results" it means that I can to "other things" and the general_exception is not handled. So... no error raised. — milheiros
– milheiros, Commented Sep 30, 2015 at 15:20
Are you sure the exception is actually being raised? It never will be by the code you posted of course. Hopefully your real p2 doesn't handle the exception itself. — Alex Poole
– Alex Poole, Commented Sep 30, 2015 at 15:24

Boneist · Accepted Answer · 2015-09-30 15:47:55Z

3

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.

answered Sep 30, 2015 at 15:47

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

milheiros Over a year ago

I found the problem. In my real procedures, I have some code who calls other procedures who have exception when others statement. That's why p2 never handle the error. Thank you very much to both for your answers. As I said, this was really a simple problem. I missed the focus! :)

Boneist Over a year ago

No worries; it's easily done! We've all been there *{:-)

Collectives™ on Stack Overflow

PL\SQL Exceptions handling between functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related