Previous Table of Contents Next


While the predefined exceptions are quite useful and deal with the most commonly encountered error conditions quite well, it’s not uncommon for other errors to be encountered as well. To allow you to handle specified errors that don’t have a predefined exception, Oracle has provided the OTHERS exception handler.

Listing 4.21 illustrates the use of an OTHERS exception handler.

Listing 4.21 Using the OTHERS exception handler.

BEGIN
  <statements>

EXCEPTION
  WHEN OTHERS THEN
      <error handling code>
END;

The code that follows the OTHERS exception handler is written to handle errors that you don’t expect. Often, this code is a call to another procedure that logs a message to a table containing specific information about the error (the text of the Oracle error, the parameters of the procedure or function in which the error occurred, and any other relevant information).

You can also use the OTHERS exception handler in conjunction with the SQLERRM() and SQLCODE() functions that PL/SQL provides, as shown in Listing 4.22.

Listing 4.22 Using SQLCODE() and SQLERRM() in an OTHERS exception handler.

BEGIN
  <statements>

EXCEPTION
  WHEN OTHERS THEN
      IF (SQLCODE = -942) THEN
        DBMS_Output.Put_Line (SQLERRM);

      ELSE
        RAISE;
      END IF;
END;

This example calls the SQLCODE() function to determine what error is occurring and SQLERRM() to record the text of the error message.

Using RAISE

You can cause exceptions to be raised in your code by using the RAISE statement. Listing 4.23 illustrates the use of the statement.

Listing 4.23 Using the RAISE statement in your code.

BEGIN
   IF <some condition> THEN
      RAISE xABORT_PROCEDURE;
   END IF;

EXCEPTION
  WHEN xABORT_PROCEDURE THEN
       ROLLBACK;
END;


Using User-Defined Exceptions
It’s a good idea not to explicitly raise the predefined Oracle exceptions. Doing so confuses the debugging process. Consider the following example:
FOR StudentClasses_rec IN StudentClasses_cur LOOP
  SELECT course_hours
  INTO   iCourseHours
  FROM   CLASSES
  WHERE  course_number = iCourseID;

  iTotalClasses := iTotalClasses + 1;
END LOOP;

IF (iTotalClasses = 0) THEN
  RAISE NO_DATA_FOUND;
END IF;

If your code looks like this example and you’re receiving a NO_DATA_FOUND exception, you can’t be certain where the exception is originating without doing some extra debugging work. It would be much better to define an appropriately named user-defined exception, say xSTUDENT_HAS_NO_CLASSES, and explicitly raise that exception instead.

If you must raise an exception under a certain condition, take advantage of the power and flexibility of user-defined exceptions.


Using Raise_Application_Error()

Oracle provides the Raise_Application_Error() procedure to allow you to raise custom error numbers within your applications. You can generate errors and their associated text starting with -20000 and proceeding through -20999 (a grand total of 1,000 error numbers that you can use). Listing 4.24 illustrates the use of the Raise_Application_Error() procedure.

Listing 4.24 Using the Raise_Application_Error() procedure.

DECLARE
  Balance   integer := 24;

BEGIN
  IF (nBalance <= 100) THEN
      Raise_Application_Error (-20343, 'The balance is too low.');
  END IF;
END;

In this example, error number -20343 is raised if the value of nBalance isn’t greater than 100, yielding a message that looks like this:

ORA-20343: The balance is too low.

Documenting Procedures

The essential elements of documentation are the same for both procedures and functions. Your documentation must cover the following three basic aspects of the procedure:

  Purpose — What business rules does the procedure enforce? Are there any special situations that the procedure has to handle?
  Parameters — What are the parameters, and how are they used? Are any of the parameters restricted with regard to size or values?
  Error conditions — What exceptions can the procedure propagate to the calling module?

The most common and useful place to document a procedure is within the procedure’s source code. This is accomplished through the use of header text, meaningfully named identifiers, and thorough commenting. It’s also useful to write pseudocode for the procedure before the code is written. This pseudocode can often be turned into a template for the comments that need to be placed in the code.


Previous Table of Contents Next