Home » SQL & PL/SQL » SQL & PL/SQL » Propagate Exception between procedure and function
Propagate Exception between procedure and function [message #203500] Wed, 15 November 2006 05:34 Go to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Hi guys,
I have a package with a procedure that call a function.

If I have a customized exception in the function, how can i propagate it in the procedure ??
Re: Propagate Exception between procedure and function [message #203502 is a reply to message #203500] Wed, 15 November 2006 05:44 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Use the RAISE command.
Re: Propagate Exception between procedure and function [message #203514 is a reply to message #203500] Wed, 15 November 2006 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Define the exception outside the function, at the top of the package body.
That way, the same exception will be in scope inside both the procedure and the function, and you will be able to trap it in the same way as any named exception.
Re: Propagate Exception between procedure and function [message #203535 is a reply to message #203500] Wed, 15 November 2006 07:51 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Ok...it seems works
Quote:

CREATE OR REPLACE PACKAGE BODY TRY_PACK IS

CHAR_NOT_IN_RANGE exception;

FUNCTION TRY_FUN (input_str VARCHAR2) RETURN VARCHAR2 IS -- Function called by TRY_PRC

...
...
...

RAISE CHAR_NOT_IN_RANGE;

...
...
...

END TRY_FUN;

PROCEDURE TRY_PRC (id IN NUMBER) IS -- The procedure that call the function TRY_FUN

...
...
...

-- rescode and resdescr are output parameters

EXCEPTION
WHEN CHAR_NOT_IN_RANGE THEN
rescode := 1;
resdescr := 'Unrecognize char';

WHEN OTHERS THEN
resCode := 2;
resDescr := 'Generic error: '||TO_CHAR(SQLCODE)||':'||SQLERRM;
END TRY PRC;



But when it raises the exception it returns...

Quote:
Generic error: 1:User-Defined Exception


...and not my rescode and resdescr parameters

???? Why ????

[Updated on: Wed, 15 November 2006 08:45]

Report message to a moderator

Re: Propagate Exception between procedure and function [message #203549 is a reply to message #203500] Wed, 15 November 2006 08:47 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

OK...It works now Smile

Thank you very much!
Re: Propagate Exception between procedure and function [message #203554 is a reply to message #203535] Wed, 15 November 2006 08:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I call User Error.
How are you passing the Rescode and ResDescr back?

It all works for me:
CREATE OR REPLACE PACKAGE EXCEPTION_TEST AS
  procedure call_proc;
  
  function  call_func return varchar2;
END;

CREATE OR REPLACE PACKAGE body EXCEPTION_TEST AS
  e_error   exception;

  function call_func return varchar2 is
  begin
    raise e_error;
    return null;
  end call_func;

  procedure  call_proc is
    v_return  varchar2(30);
  begin
    v_return := call_func;
  exception
    when e_error then
      raise_application_error(-20001,'UDE caught. Sqlcode '||sqlcode||' sqlerrm '||sqlerrm);
    when others then
      raise_application_error(-20001,'Others caught. Sqlcode '||sqlcode||' sqlerrm '||sqlerrm);
  end call_proc;

END;

SQL> begin
  2    exception_test.call_proc;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20001: UDE caught. Sqlcode 1 sqlerrm User-Defined Exception
ORA-06512: at "JOHN_TEST.EXCEPTION_TEST", line 16
ORA-06512: at line 2

As you can see, it catches the exception. Internally, SQLCode for userdefined exceptions is 1, and SQLErrm is 'User-Defined Exception', but as I show, you can change that in the exception block with Raise_Application_Error.

Re: Propagate Exception between procedure and function [message #203559 is a reply to message #203500] Wed, 15 November 2006 09:20 Go to previous message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

It didn't work because I also declared the exception in TRY_PRC code...

Quote:
CHAR_NOT_IN_RANGE exception;


...that was declared in the package body.

I've delete that declare and now it works with my code Wink

[Updated on: Wed, 15 November 2006 09:21]

Report message to a moderator

Previous Topic: Oracle query
Next Topic: case or if clause in where statement
Goto Forum:
  


Current Time: Sat Dec 03 00:51:44 CST 2016

Total time taken to generate the page: 0.11573 seconds