Home » SQL & PL/SQL » SQL & PL/SQL » Exception Handling
Exception Handling [message #267722] Fri, 14 September 2007 06:30 Go to next message
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 #267725 is a reply to message #267722] Fri, 14 September 2007 06:38 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

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.
Re: Exception Handling [message #267731 is a reply to message #267722] Fri, 14 September 2007 07:18 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Thanks for your reply but I'd only need to use the PRAGMA to associate an error number with the exception, and as I know what and where it is that wouldn't solve my problem.

However I have found out what it is - the calling package doesn't handle this exception. Rolling Eyes
Re: Exception Handling [message #267742 is a reply to message #267725] Fri, 14 September 2007 10:15 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: conversion
Next Topic: Insert 1 million records using SQLPLUS
Goto Forum:
  


Current Time: Tue Feb 11 09:49:27 CST 2025