Oracle Tips by
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
Listing 8.6 Code using a tracepoint variable.
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),
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
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
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.