Oracle PL/SQL Best Practices

Name

EXC-06: Do not overload an exception with multiple errors unless the loss of information is intentional.

Synopsis

Don’t declare one generic exception such as bad_data and then raise that exception under different circumstances. Users of your code will have trouble understanding precisely what caused the problem. Instead, declare a separate exception for each different kind of failure.

Example

Oracle is guilty of violating this best practice, as can be seen with NO_DATA_FOUND. This exception can be raised by a SELECT INTO that finds no rows, by attempting to read an undefined row in a collection, or by reading past the end of a file. How can you tell what went wrong inside your NO_DATA_FOUND handler? This dilemma is shown in this example:

CREATE OR REPLACE PROCEDURE two_reads
IS
   l_title book.title%TYPE;
   l_line VARCHAR2(1023);
   fid UTL_FILE.FILE_TYPE;
BEGIN
   SELECT title INTO l_title
     FROM emp
    WHERE 1 = 2;

   fid := UTL_FILE.FOPEN (
      'c:\temp', 'justoneline.txt', 'R');
   UTL_FILE.GET_LINE (fid, l_line);
   UTL_FILE.GET_LINE (fid, l_line);

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      pl ('Who did that?');
END two_reads;

If you do run into situations like this, whether due to Oracle’s design or another developer in your organization, you can use nested blocks to avoid the ambiguity. By declaring a block around a set of lines of code, you can restrict the propagation of the ambiguous exception and transform that exception into a unique identifier. Here’s an example of this approach:

CREATE OR REPLACE PROCEDURE ...