 |
|
Using Tracepoints
Oracle Tips by
Burleson
|
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
