Oracle Tips by
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
CREATE OR REPLACE
WHERE graduation_date IS NOT NULL;
FOR Active_Student_rec IN Active_Students_cur LOOP
iTracePoint := 1;
SELECT sum (course_hours),
sum (decode (course_grade, 'A', 4,
INTO iTotalHours, iTotalCredits
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;
SET overall_gpa = nNewGPA
WHERE ssn = Active_Student_rec.ssn;
iTracePoint := 4;
WHEN OTHERS THEN
SQLERRM || ' at tracepoint ' || to_char (iTracePoint),
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.