265-269

Previous

Table of Contents

Next

Using Tracepoints

In my opinion, a better method of debugging runtime errors is the use of a tracepoint variable to keep track of an object s current location. Listing 8.6 is a sample of code that uses a tracepoint variable.

Listing 8.6 Code using a tracepoint variable.

 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; / 

Naturally, this approach takes some time and work to implement, but for complex code the return on investment is exceptionally high. This method has several advantages to its credit:

   You are always aware of an error s location within the object. Each statement has a unique tracepoint value, so when a statement is referenced, it must be the cause of the runtime error ”regardless of how innocuous -looking that statement may be.

   You always know which record the object was processing when the error occurred. Spending time looking up the various calls to the object and adding debugging code to determine parameter values wastes time that can be spent fixing the code.

   The error code and message are preserved for reference. Knowing what type of error occurred might save the trouble of even testing the single statement in question.

   You always know the object in which the error occurred. Knowing this can save time when debugging combinations of objects.

Saving these pieces of information is essential if the code is to be repaired quickly. Of course, this approach only works if you don t mind doing extra work up front, but those with the patience and time to implement it properly can save hours of debugging time down the line.

This approach is most effective when your code is designed to handle errors effectively.

Handling Exceptions Cleanly During Execution

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.

Previous

Table of Contents

Next