|Previous||Table of Contents||Next|
The best use of the OTHERS exception handler is to record that an error has occurred and to prevent damage to the data as a result of the error. The exception handler in Listing 8.8 does that by recording the event to a SYSTEM_ERRORS table.
Listing 8.8 Using the OTHERS exception handler to log an error.
CREATE OR REPLACE PROCEDURE Calculate_Student_Grades IS CURSOR Active_Students_cur IS SELECT ssn FROM STUDENTS WHERE graduation_date IS NOT NULL; vCurrentSSN STUDENTS.ssn%TYPE; nNewGPA STUDENTS.overall_gpa%TYPE; iTracePoint integer; BEGIN FOR Active_Student_rec IN Active_Students_cur LOOP iTracePoint := 1; SELECT sum (course_hours), sum (decode (course_grade, 'A', 4, 'B', 3, 'C', 2, 'D', 1)) INTO iTotalHours, iTotalCredits FROM ENROLLED_CLASSES WHERE ssn = Active_Student_rec.ssn AND nvl (credit_flag, 'Y') = 'Y' AND nvl (audit_flag, 'N') = 'N' AND course_complete <= SYSDATE; iTracePoint := 2; nNewGPA := iTotalCredits / iTotalHours; iTracePoint := 3; UPDATE STUDENTS SET overall_gpa = nNewGPA WHERE ssn = Active_Student_rec.ssn; END LOOP; iTracePoint := 4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; INSERT INTO SYSTEM_ERRORS (error_time, error_parameters, display, error_text, error_number, error_object) VALUES (SYSDATE, Active_Student_rec.ssn, 'N', SQLERRM || ' at tracepoint ' || to_char (iTracePoint), SQLCODE, 'Calculate_Student_Grades'); COMMIT; END Calculate_Student_Grades; /
Notice the steps taken by the exception handler and the order in which they occur:
In this example, the SYSTEM_ERRORS table is a custom table implemented to aid in debugging runtime errors. This isnt one of Oracles data dictionary tables.
Of course, its possible that there may be other changes pending to the database that didnt originate in this object. In this event, the OTHERS exception handler should still record the information about the error before raising the exception to the calling object. The decision about whether or not to roll back the changes can then be made by the calling object.
Your exception handlers will often include calls to some built-in error handling functions in PL/SQL.
PL/SQL provides two useful functions that allow you to identify errors: SQLCODE() and SQLERRM(). These functions are specific to PL/SQL and cant be used in your SQL statements; attempting to do so will result in an error. However, the output of these functions can be assigned to variables in your PL/SQL blocks. Lets take a look at each function.
Using The SQLCODE() Function
The SQLCODE() function is used to return the number of the most recent Oracle error message during the execution of a PL/SQL block. For instance, a reference to a nonexistent table or view causes Oracle error ORA-00942 to occur. For this error, the SQLCODE() function would return:
Listing 8.9 illustrates how the SQLCODE() function can be used in your exception handlers.
Listing 8.9 Calling the SQLCODE() function in an exception handler.
EXCEPTION WHEN OTHERS THEN IF (SQLCODE = -942) THEN RAISE xMISSING_TABLE; ELSE ROLLBACK; INSERT INTO SYSTEM_ERRORS (error_time, error_parameters, display, error_text, error_number, error_object) VALUES (SYSDATE, Active_Student_rec.ssn, 'N', SQLERRM || ' at tracepoint ' || to_char (iTracePoint), SQLCODE, 'Calculate_Student_Grades'); COMMIT; END IF; END;
The error numbers returned by this function are always negative.
Using The SQLERRM() Function
The SQLERRM() function returns the complete text of the last Oracle error message to occur during the execution of a block of PL/SQL code. Calling this function is quite simple:
vErrorText := SQLERRM;
Calling this function when no errors have occurred returns:
ORA-0000: normal, successful completion
Debugging is a skill that relies heavily on a developers experience and familiarity with the code. Debugging is the largest part of a developers job and probably the most tedious aspect. This chapter covers several techniques that can ease the burden of debugging your code if you plan ahead when youre writing the code.
|Previous||Table of Contents||Next|