Oracle PL/SQL Best Practices

Name

EXC-14: Use the EXCEPTION_INIT pragma to name system exceptions that might be raised by your program.

Synopsis

There are hundreds upon hundreds of Oracle error codes and messages. Only a small handful are actually assigned a name for use in the PL/SQL language. This assignment occurs in the STANDARD package; here, for example, is the code defining the first three named exceptions in that package:

CURSOR_ALREADY_OPEN exception;
  pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');

DUP_VAL_ON_INDEX exception;
  pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');

TIMEOUT_ON_RESOURCE exception;
  pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');

And since STANDARD is the default package, you can then write code in your own programs like:

EXCEPTION
   WHEN CURSOR_ALREADY_OPEN THEN ...

You can also give names to system exceptions, and you should do so when your program might raise one of those exceptions.

Example

When I built PLVdyn, a PL/Vision package that makes it easier to execute dynamic SQL, I gave names to a number of errors that commonly occur when constructing and executing SQL strings with DBMS_SQL. I realized that no matter how good my code was, a user might pass a dynamic string that, for example, referenced an undefined table or column. Without a named exception, you ended up writing code like this:

BEGIN
   cur := PLVdyn.open_and_parse ('SELECT ... ');
   ...
EXCEPTION
   WHEN OTHERS
   THEN
      IF SQLCODE = -904 THEN -- invalid column name
         ...
      ELSIF SQLCODE = -942 THEN - no such table

and so on. ...