Exception Handling [message #267722] |
Fri, 14 September 2007 06:30  |
scorpio_biker
Messages: 154 Registered: November 2005 Location: Kent, England
|
Senior Member |
|
|
Hi,
I have an issue at the moment with some exception handling in a couple of packages and I wonder if someone can point me in the right direction.
My Oracle version is Oracle9i Enterprise Edition Release 9.2.0.7.0
but we are recent converts from 7.3
I have two packages A and B
B declares an exception in its specification
CD_Card_Expired Exception
A runs and calls B and B raises the exception.
if Card_Row.Expires_Date < On_Date then
raise CD_Card_Expired;
End if;
Control then returns to A and the exception is checked for
when B.CD_Card_Expired then
raise;
However when the code gets to the raise it fails with a
ORA-6510 PL/SQL : unhandled user-defined exception.
Even if I add the exception name to the raise it still fails with the same error.
In order to test the calling of the exception from outside the package I set up a small test script
begin
begin
dbms_output.put_line('raise exception');
raise b.CD_Card_Expired;
exception
when b.CD_Card_Expired then
dbms_output.put_line('raise again');
raise;
end;
exception
when b.CD_Card_Expired then
dbms_output.put_line('Got my exception');
end;
and this works fine.
If anyone can offer any pointers I would appreciate it.
|
|
|
|
|
Re: Exception Handling [message #267742 is a reply to message #267725] |
Fri, 14 September 2007 10:15   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Soumen Kamilya wrote on Fri, 14 September 2007 13:38 | You can use pragma to raise user defined exception.
For example:
CD_Card_Expired Exception;
PRAGMA EXCEPTION_INIT(CD_Card_Expired, -01400);
Here -01400 is the exception no defined by USER.
Where you track this exception it will show the error.
|
You probably used this purely as an example, but for the people that are planning to use this, a little addition:
If you define your own exceptions, bind them to an errornumber >= 20000.
These numbers are reserved for user-defined errors, so you will not redefine existing Oracle-errors without knowing.
|
|
|
Re: Exception Handling [message #267948 is a reply to message #267742] |
Sun, 16 September 2007 22:01  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
You say that A contains a line:
when B.CD_Card_Expired then
raise;
The RAISE statement re-raises the same exception. So whatever calls 'A' would also need to handle the user-defined exception.
Ultimately there needs to be a PL/SQL block somewhere in the call-stack that does something with B.CD_Card_Expired except RAISE it back again.
Ross Leishman
|
|
|