Previous | Table of Contents | Next |
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.
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:
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 .
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:
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.
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 |