Logging Errors

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.

Useful Functions

PL/SQL provides two useful functions that allow you to identify errors: SQLCODE() and SQLERRM(). These functions are specific to PL/SQL and can’t 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. Let’s 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:

-00942

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

Summary

Debugging is a skill that relies heavily on a developer’s experience and familiarity with the code. Debugging is the largest part of a developer’s 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 you’re writing the code.