Previous Table of Contents Next


Chapter 8
Debugging

Preceding chapters have intentionally left out the subject of debugging SQL and PL/SQL code. Debugging is an essential skill for all application developers, including those who use SQL and PL/SQL. This chapter consolidates knowledge gained from the implementation of several approaches to debugging PL/SQL applications.

Many of the techniques illustrated here work most effectively if you plan ahead when writing your code. Because going back and rewriting existing code is seldom an option, there are also some techniques presented that will help out when you are debugging code you didn’t design.

This chapter divides errors into two classes: compile errors and runtime errors. Of the two types of errors, compile errors are the easiest to resolve, so we’ll cover those first.

Compile Errors

A compile error occurs when a statement is being parsed. The following are all examples of problems in code that can cause a compile error:

  A missing semicolon at the end of an SQL or PL/SQL statement.
  An SQL statement that references a nonexistent table.
  A reference to a variable that hasn’t been declared.
  An incorrect CREATE command.

This list is by no means exhaustive. Some of these examples are simple mistakes (“Drat, I keep forgetting those darned semicolons.”). Others are caused by poor documentation (“This is the table mentioned in the design document, but it has been called something else in the system.”). Still others will help you isolate system problems (“I’m referencing this table in my code, but Oracle says the table doesn’t exist. I think I need a synonym for the table.”).

Fortunately, resolving compile errors is usually very simple. The first step to being able to successfully resolve a compile error is knowing how to find it. Oracle is a big help here, since it reports the position of the compile error if you ask nicely.

Listing Compile Errors

When compiling code inside SQL*Plus, you can show the compile errors for a block of code using the show errors command, as follows:

show errors

Let’s assume that you’re trying to compile the stored procedure shown in Listing 8.1.

Listing 8.1 A sample stored procedure with compile errors.

CREATE OR REPLACE
PROCEDURE Calculate_Student_Grades

IS

   CURSOR Active_Students_cur
   IS
   SELECT ssn
   FROM   STUDENTS
   WHERE  graduation_date IS NOT NULL;

   Active_Student_rec         Active_Students_cur%ROWTYPE;

   vCurrentSSN                STUDENTS.ssn%ROWTYPE;
   nNewGPA                    STUDENTS.gpa%TYPE;

BEGIN
   FOR Active_Student_rec IN Active_Student_cur LOOP
       nNewGPA := Calculate_GPA (vSSN => vCurrentSSN);
   END LOOP;
END Calculate_Student_Grades;
/

When trying to compile this procedure in SQL*Plus, the message

Warning: Procedure created with compilation errors.

is received. In order to identify the compile errors, you must execute the show errors command, as shown in Figure 8.1.


Figure 8.1  Using the show errors command.

Figure 8.1 shows the following three compile errors:

  The vCurrentSSN variable shouldn’t be using a %ROWTYPE reference to the GPA column in the STUDENTS table.
  There is no column named GPA in the STUDENTS table.
  The reference to the Active_Students_cur cursor on line 13 is misspelled. The compiler cannot isolate the reference to the Active_Student_cur cursor because it hasn’t been declared.

At this point, the Calculate_Student_Grades() procedure actually exists within Oracle. The procedure is marked as invalid because it failed to compile, but pulling the source code for the procedure from the ALL_SOURCE view will give you the most recently compiled code. Now that you know how to locate compile errors in your code, let’s move on to correcting the errors.

Fixing Compile Errors

Now that the compilation errors for the Calculate_Student_Grades() procedure have been identified, the source code has to be modified to fix the errors so the procedure can be recompiled. The revised source code that fixes these compile errors is shown in Listing 8.2.

Listing 8.2 The revised Calculate_Student_Grades() procedure.

CREATE OR REPLACE
PROCEDURE Calculate_Student_Grades

IS

   CURSOR Active_Students_cur
   IS
   SELECT ssn
   FROM   STUDENTS
   WHERE  graduation_date IS NOT NULL;

   Active_Student_rec         Active_Students_cur%ROWTYPE;

   vCurrentSSN                STUDENTS.ssn%TYPE;
   nNewGPA                    STUDENTS.overall_gpa%TYPE;

BEGIN
   FOR Active_Student_rec IN Active_Students_cur LOOP
       vCurrentSSN := Active_Student_rec.ssn;
       nNewGPA := Calculate_GPA (vSSN => vCurrentSSN);
   END LOOP;
END Calculate_Student_Grades;
/

Figure 8.2 illustrates what happens when we attempt to recompile the procedure now.


Figure 8.2  Compiling the Calculate_Student_Grades() procedure after fixing compile errors.

As you can see, there is still at least one compile error in the source code. Once again, we need to issue the show errors command to see which compilation errors have occurred. The result of the show errors command is shown in Figure 8.3.


Figure 8.3  Compile errors in the Calculate_Student_Grades() procedure.

Oops! It looks like the Calculate_GPA() function doesn’t exist. Resolving this compile error requires finding out what happened to the function. Is it in another schema? Does the function even exist?

This process is repeated as necessary to obtain a clean compile for the procedure.


Previous Table of Contents Next