Oracle PL/SQL Best Practices

Name

EXC-04: Use your own raise procedure in place of explicit calls to RAISE_APPLICATION_ERROR.

Synopsis

When it comes to managing errors, Oracle requires a lot of developers. If you’re raising a “system” exception like NO_DATA_FOUND, you use RAISE. But when you want to raise an application-specific error, you use RAISE_APPLICATION_ERROR. If you use the latter, you have to provide an error number and message. This leads to unnecessary and damaging hard coding (see [EXC-09: Use named constants to soft-code application-specific error numbers and messages.]).

A more fail-safe approach is to provide a predefined raise procedure that automatically checks the error number and determines the correct way to raise the error. An example of such a procedure may be found in the err.pkg file on the Oracle PL/SQL Best Practices web site, and is described briefly in the following section.

Example

Instead of writing code like this:

RAISE_APPLICATION_ERROR (
   -20734,
   'Employee must be 18 years old.');

you should write code like this:

err.raise (errnums.emp_too_young);

Here’s an example of how you might construct a generic exception raiser (from err.pkg ):

 PROCEDURE raise ( errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL ) IS l_errcode PLS_INTEGER := NVL (errcode, SQLCODE); l_errmsg PLS_INTEGER := NVL (errmsg, SQLERRM); BEGIN IF l_errcode BETWEEN -20999 AND -20000 THEN RAISE_APPLICATION_ERROR (l_errcode, l_errmsg); /* Use positive error numbers -- lots to choose from! */ ELSIF l_errcode > 0 ...