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