Oracle PL/SQL Best Practices

Name

EXC-05: Only RAISE exceptions for errors, not to branch execution control.

Synopsis

The RAISE statement is an easy and powerful way to abort normal processing in a program and immediately “go to” the appropriate WHEN handler. You should, however, never use RAISE in this way. You should raise an exception only when an error has occurred, not to control program flow.

Example

Here’s a program that demonstrates the problem; it performs a full table scan of a collection and immediately exits when it finds a match. The exit_function exception aborts the function if the input title is NULL; it’s also used as the last line in the function:

CREATE OR REPLACE FUNCTION book_from_list (
   list_in IN book_tabtype,
   title_in IN book.title%TYPE)
RETURN book%ROWTYPE 
IS
   exit_function EXCEPTION;
BEGIN
   IF title_in IS NULL
   THEN
      RAISE exit_function;
   END IF;

   FOR indx IN list_in.FIRST .. list_in.LAST
   LOOP
      IF list_in(indx).title = title_in
      THEN
         RETURN list_in(indx);
      END IF;
   END LOOP;

   RAISE exit_function;

EXCEPTION
   WHEN exit_function THEN RETURN NULL;
END;

Whew. Strange stuff. You manage to make it all the way to the end of the function, and then you are punished by having an exception raised! This is very poorly structured code: hard to understand and hard to maintain.

Here’s a better approach:

CREATE OR REPLACE FUNCTION book_from_list ( list_in IN book_tabtype, title_in IN book.title%TYPE) RETURN book%ROWTYPE IS indx PLS_INTEGER; retval book%ROWTYPE; BEGIN IF title_in IS NOT NULL THEN indx := list_in.FIRST; ...