 |
|
Logging Errors
Oracle Tips by
Burleson
|
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:
1. Roll back
any pending changes to the database.
2. Record the
error message, location, and any data that could be of use when
attempting to resolve the error.
3. Commit the
data about the error message.
In this example, the SYSTEM_ERRORS table is a custom table
implemented to aid in debugging runtime errors. This isn’t one of
Oracle’s data dictionary tables.
Of course, it’s possible that there may be other changes pending to
the database that didn’t 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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
