134-137

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