Feb
3
Written by:
StevenFeuersteinTW
Friday, February 03, 2012 10:54 AM
Oracle PL/SQL provides the RAISE_APPLICATION_ERROR built-in procedure (actually defined in the DBMS_STANDARD package) so that we can communicate application-specific error messages back to our users. When you call this procedure, you provide a negative number between -20999 and -20000, as well as your error message.
Here's an example of calling RAISE_APPLICATION_ERROR from inside a trigger, ensuring that employees must be at least 18 years old.
BEGIN
IF :new.birth_date > ADD_MONTHS (SYSDATE, -18 * 12)
THEN
RAISE_APPLICATION_ERROR (-20701
, 'Employee must be at least 18 years old.');
END IF;
END;
For many programmers, having 1,000 error codes to choose from is more than enough, but I have met developers over the years who have "run out" of error codes. Surely, Oracle could have let us use a few more of the infinite number of negative integers "out there." And if they are not going to do that, then at least you would think that Oracle would not itself use any of these "precious" user error codes.
That is, however, not the case. Oracle itself uses RAISE_APPLICATION_ERROR in a number of its own supplied packages. But which error codes does Oracle use?
The 29 December 2011 quiz at the PL/SQL Challenge, tested players' knowledge of the different ways that errors can be raised in a PL/SQL block. As has been the case with many quizzes, it served as a launch point for an interesting discussion, which you can read here. It also led Niels Hecker, one of the top players at the PL/SQL Challenge, to figure out exactly which of those -20NNN error codes are used by Oracle. Well, to be honest, he wasn't quite able to do that. Instead he finds all the PRAGMA EXCEPTION_INIT statements that reference an error code in that range.
I offer Niels' query below, but please note that you will need access to the DBA_SOURCE data dictionary view. You can change it to USER_SOURCE, but then you will "only" identify such usages in your own code – which is useful in and of itself. I have also restricted the search to objects owned by SYS and SYSMAN, so you will want to remove that WHERE clause predicate for a wider search.
WITH data
AS (SELECT ( '^.*PRAGMA\s+EXCEPTION_INIT\s*\(\s*' -- PRAGMA EXCPETION_INIT(
|| '([A-Z0-9_$#]{1,30}|"[^"]{1,30}")' -- Exception-Name
-- comment the following line out, if the exception_name
-- and the exception code are on different lines
|| '\s*,\s*''?(-?\d{1,5})''?' -- Exception-Code
-------
|| '.*$' -- End-of-Line
)
AS rx
, (' ' || CHR (10) || CHR (13) || CHR (9)) AS trimset
FROM DUAL)
, data_source
AS (SELECT s.owner
, s.TYPE
, s.name
, s.line
, UPPER (RTRIM (s.text, (' ' || CHR (10) || CHR (13)))) AS text
FROM data d, dba_source s
WHERE REGEXP_LIKE (s.text, d.rx, 'i'))
, data_prepared
AS (SELECT ds.owner
, ds.TYPE
, ds.name
, ds.line
, ds.text
, REGEXP_REPLACE (ds.text, d.rx, '\1') AS exname
, REGEXP_REPLACE (ds.text, d.rx, '\2') AS excode
FROM data d, data_source ds)
SELECT dp.excode
, dp.owner
, dp.TYPE
, dp.name
, dp.exname
, dp.line
, dp.text
FROM data_prepared dp
WHERE (TO_NUMBER (dp.excode) BETWEEN -20999 AND -20000)
AND dp.owner IN ('SYS', 'SYSMAN')
ORDER BY TO_NUMBER (dp.excode) DESC
, dp.owner
, dp.TYPE
, dp.name;
Oracle could still use RAISE_APPLICATION_ERROR in one of its package bodies to simply raise an error in that range, without associating it with a named exception. There isn't really any way to find those, since almost all built-in package bodies are wrapped (obfuscated).