Read an Excerpt
3: Tracing and Debugging
Very few programs perform correctly the first time they are written. Also, the requirements for a program often change during development, and the program must be rewritten. In either case, the program needs to be tested thoroughly to make sure that it is working properly and performing as expected. In this chapter, we will discuss several techniques for testing and debugging PL/SQL programs, including both non-graphical techniques and various graphical PL/SQL debuggers. We will also examine the tracing and profiling tools available with Oracle8i.
(1)Problem Diagnosis
Every bug is different from the last, which is what makes debugging and testing a challenge. You can reduce the occurrences of bugs through testing and QA analysis during development, but if you do program development for a while, you will almost certainly have to find the bugs and errors in your own or somebody else's code.
(2)Debugging Guidelines
Even though every bug is different, and there could be many fixes for any given bug, the process of finding and fixing bugs can be defined. Over the past few years of debugging both my own and other programmers' code, I have developed several guidelines for determining the cause of a problem. These guidelines can be applied to development in any programming language, not just PL/SQL.
(3)Find the Place Where the Error Occurs
Obvious though it may seem, finding the place where the error occurs is crucial to fixing coding problems. If you have a large, complicated program that simply fails, the first step is to determine exactly where the failure is occurring. This is not necessarily an easy task, depending on the complexity of the code. The easiest way to find the source of an error is to trace the program as it runs, examining the values of the data structures to determine what went wrong.
(3)Define Exactly What Is Wrong
Once you know where the problem happens, you need to define exactly what the problem is. Is an Oracle error returned? Does a calculation return the wrong result? Does the wrong data get inserted into the database? In order to fix a bug, it is necessary to know how the bug manifests itself.
(3)Reduce the Program to a Simple Test Case
A good strategy to follow when you don't know where the error is occurring is to reduce the program to a simple test case. Start cutting out parts of the code and rerun the program. If the error still occurs, you know that the section you removed did not cause the error. If the error goes away, examine the section that was removed.Remember that one area of your code may have the bug, but the problem may manifest itself in another part of the code. For example, a procedure may return an incorrect value, but the returned value is not actually used until later in the main program. The problem here is not the main program (where the error appears to be) but in the procedure. Cutting out the procedure call and replacing it with a direct assignment to the returned value would reveal the source of the problem. We will examine this particular case later in this chapter.
(3)Establish a Testing Environment
Ideally, testing and debugging is not done in a production environment. It is a good idea to maintain a testing environment that duplicates production as much as possible-the same database structure but with less data, for example. This way you can develop and test newer versions of your application without affecting the production version that is already running. If a problem occurs in production, try reproducing the problem in test first. This follows the previous principle of reducing the problem to a smaller test case. A test case may involve more than just the code-PL/SQL is very dependent on the database structure and the contents of the data, and these should be reduced as well.
(2)The Debug Package
PL/SQL is designed primarily for manipulation of data stored in an Oracle database. The structure of the language is based on this use and it performs admirably. For practical purposes, however, we need some additional tools to help write and debug programs.In the next sections, we will examine in detail different methods of debugging PL/SQL code. Each section focuses on a different problem and uses a different method to isolate the problem following the guidelines just given. Each section will first describe the general debugging method, then give a description of the problem to be solved. We will discuss both nongraphical and graphical debugging techniques. In the course of solving the nongraphical problems, we will develop different versions of a debugging package, Debug, which you can use in your own programs. Depending on your environment and needs, the different capabilities of each package will be useful.
(1)Nongraphical Debugging Techniques
Although there are many graphical debuggers available for PL/SQL (we will examine them later in this chapter), there are times where simple text-based debugging is helpful. GUI-based tools are not always available, or may not be able to be set up for complicated PL/SQL runtime environments. The two techniques we will discuss in this chapter-inserting into a test table, and printing to the screen-are simple and require no additional software beyond the capabilities of PL/SQL itself.
(2)Inserting into a Test Table
The simplest method of debugging is to insert the values of local variables into a temporary table as the program is running. When the program has completed, you can query the table to determine the values of the variables. This method requires the least effort to implement and will work regardless of the execution environment, since it simply involves extra INSERT statements.
(3)Problem 1
Suppose we want to write a function that will return the average grade for each class, based on the currently registered students. We could write this function as follows:L 3-1
-- Available online as AverageGrade1.sql
CREATE OR REPLACE FUNCTION AverageGrade (
/* Determines the average grade for the class specified. Grades are
stored in the registered_students table as single characters
A through E. This function will return the average grade, again,
as a single letter. If there are no students registered for
the class, an error is raised. */
p_Department IN VARCHAR2,
p_Course IN NUMBER) RETURN VARCHAR2 AS
v_AverageGrade VARCHAR2(1);
v_NumericGrade NUMBER;
v_NumberStudents NUMBER;
CURSOR c_Grades IS
SELECT grade
FROM registered_students
WHERE department = p_Department
AND course = p_Course;
BEGIN
/* First we need to see how many students there are for
this class. If there aren't any, we need to raise an error. */
SELECT COUNT(*)
INTO v_NumberStudents
FROM registered_students
WHERE department = p_Department
AND course = p_Course;
IF v_NumberStudents = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
p_Department || ' ' || p_Course);
END IF;
/* Since grades are stored as letters, we can't use the AVG
function directly on them. Instead, we can use the DECODE
function to convert the letter grades to numeric values,
and take the average of those. */
SELECT AVG(DECODE(grade, 'A', 5,
'B', 4,
'C', 3,
'D', 2,
'E', 1))
INTO v_NumericGrade
FROM registered_students
WHERE department = p_Department
AND course = p_Course;
/* v_NumericGrade now contains the average grade, as a number from
1 to 5. We need to convert this back into a letter. The DECODE
function can be used here as well. Note that we are selecting
the result into v_AverageGrade rather than assigning to it,
because the DECODE function is only legal in a SQL statement. */
SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
4, 'B',
3, 'C',
2, 'D',
1, 'E')
INTO v_AverageGrade
FROM dual;
RETURN v_AverageGrade;
END AverageGrade;Suppose the contents of registered_students looks like this...