269-273 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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.

Table of Contents