Re: help with exceptions please

On Sep 23, 5:24 pm, jgarfi..._at_earthlink.net wrote:
> I'm trying to understand oracle exceptions. There appear to be many
> different ways that exceptions are defined. Following is how I
> understand it. Please let me know if I'm wrong or if my terminology
> is off.
>
> 1) "oracle errors" ("application errors?") e.g. ORA-99999.
> All already have NUMBERS and MESSAGES. Some have NAMES (identifiers)
> such as ACCESS_INTO_NULL. The ones with names are the ones in the
> standard package.
>
> You can't catch an error without a name, must use EXCEPTION_INIT to
> give the number a name.
>
> 2) user defined exceptions - NO NUMBERS or MESSAGES associated with
> them, but they do have NAMES e.g.
>
> e_invalid_id EXCEPTION;
>
> 3) user defined "application errors," have NUMBER and MESSAGE but NO
> NAME. e.g.
>
> RAISE_APPLICATION_ERROR(error_number, error_message [, keep_errors])
>
> since they have no name, there is no way one could catch them using:
> EXEPTION
> WHEN error_name THEN
>
> so there's nothing you can do about them.
>
> (by the way, why would one want to keep_errors or not keep_errors
> here?)
>
> Also, you can associate an oracle error but not a custom application
> error (#3 above) with an exception name using this pragma:
>
> EXCEPTION_INIT(e_child_exists, -1234);
>
> These now have a NAME and a NUMBER, and the MESSAGE already assigned
> to that number.
>
> is this an accurate description?

You can associate any Oracle provided or user error with a name via the prgama directive.

>From PL/SQL User's Guide and Reference Release 2 (9.2) Part Number
A96624-01 Ch 7 Handling PL/SQL Errors
>>

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN
   ... -- Some operation that causes an ORA-00060 error EXCEPTION
   WHEN deadlock_detected THEN

• handle the error END; <<

As you can see this code assigns ORA-00060 to the name deadlock_detected.

You normally use the procedure raise_applcation_error(-number, 'message') to raise a user and assign it a numeric value and a message when you want to pass the information back up the calling chain.

>>

DECLARE
   acct_type INTEGER := 7;
BEGIN
   IF acct_type NOT IN (1, 2, 3) THEN

      RAISE INVALID_NUMBER; -- raise predefined exception    END IF;
EXCEPTION
   WHEN INVALID_NUMBER THEN
      ROLLBACK;
END;
<<

The manuals are your friend.

HTH -- Mark D Powell -- Received on Sun Sep 23 2007 - 17:46:23 CDT