Re: List User Defined Exception Numbers?

Mark Bole <makbo_at_pacbell.net> wrote:

>
>
>Jim McMahon wrote:
>> "Anurag Varma" <avdbi_at_hotmail.com> wrote:
>>
>>
>>>"Jim McMahon" <jp_mcmahon_at_hotmail.com> wrote in message news:4098120d.344818433_at_news.charter.net...
>>>
>>>>Does anyone know whether it is possible to list user defined exception
>>>>numbers that may be defined within the triggers, procedures, and
>>>>functions defined under a given schema? Are they stored in some sort
>>>>of internal table when the entities are compiled that could be
>>>>queried?
>>>>
>>>>What I'd like to do is list all used error numbers so that when I
>>>>write a new function or trigger and raise a newly defined application
>>>>error I can ensure the number is unique. The functions and procedures
>>>>under the schema would all be signalling the errors via
>>>>
>>>>raise_application_error(error_number, message[, {TRUE | FALSE}]);
>>>>
>>>>where error_number is a negative integer in the range -20000 .. -20999
>>>>and message is a character string up to 2048 bytes long.
>>>>
>>>>If I can't get to the error numbers directly, is there an internal
>>>>table of object source I could dump out and run a grep tool against to
>>>>list all occurences of "raise_application_error" to find the numbers
>>>>that have been previously used ?
>>>>Being ordinary and nothing special is a full-time job.
>>>>jp_mcmahon_at_hotmail.com (Jim McMahon in real life)
>>>
>>>
>>>is user_source / all_source / dba_source what you are looking for?
>>>
>>>Anurag
>
>> Originally I was hoping for something to find the used application
>> error codes only, but yes, I think user_source is what I was looking
>> for as an alternative. I'll be able to verify that later today at
>> work.
>
>Nope, no internal table, but why not make one of your own? (see
>suggestion #2 below).
>
>Grep'ing through source is better than nothing, but if you have more
>than a few user-defined errors or more than a few programmers on your
>team, consider the following alternatives.
>
>1) declare constants in a single package for each user-defined error
>that can be used throughout your code
>
> my_err_01 CONSTANT NUMBER := -20500;
> my_err_02 CONSTANT NUMBER := -20501;
>
>2) create a database table with a unique name and number for each
>user-defined error, look up the errno's you may need to raise and store
>them in variables to use with the raise_application_error call.
>
>--Mark Bole
>
>

That's a good idea - thanks. I'll have to sell my DBA on it, but I definately like the concept. It's exactly what my small team does on the application code side, using a static java class. Meanwhile, I was able to find the numbers already used via ALL_SOURCE - I got lucky and there only about 6 triggers/functions/procedures that contained the string "raise_application_error", so when I got the object names I just looked at those entities directly and jotted down the numbers.

Being ordinary and nothing special is a full-time job. jp_mcmahon_at_hotmail.com (Jim McMahon in real life) Received on Sun May 09 2004 - 07:22:07 CDT