 |
|
Handling Exceptions
Cleanly During Execution
Oracle Tips by
Burleson
|
Using exceptions thoughtfully can save you a lot of time that you
would otherwise spend writing error handling code. Just like many
other coding techniques, the best way to use the technique is to
design with the technique in mind. While this does add some overhead
to the design process, using exception handlers (particularly the
OTHERS exception handler) is essential to writing effective code.
The OTHERS Exception Handler
The OTHERS exception handler serves as a catch-all exception
handler, handling any error that falls through any other exception
handling that you have in place. This exception handler should always
be the last exception handler in your block of code.
This exception handler is a powerful tool if used properly. If used
improperly or carelessly, the OTHERS exception handler will
mask errors and make your debugging work more difficult than it needs
to be. Listing 8.7 shows how the OTHERS exception handler can
be misused.
Listing 8.7 Misusing the OTHERS exception handler.
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;
END Calculate_Student_Grades;
/
In this example, the exception handler doesn’t do anything that
will help the developer debug the application if something goes wrong.
In fact, the exception handler completely obscures the fact that
something is going wrong.
Preventing data from being altered when an error occurs is an
admirable goal, but at the end of the semester when grades go out each
student will have remarkably maintained the status quo and the
developer (or someone who has taken over the responsibilities) will
find themselves in a very hot spot. At the very least, the developer
in this case should have recorded that an error occurred.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
