Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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.

PROCEDURE Calculate_Student_Grades
   CURSOR Active_Students_cur
   SELECT ssn
   WHERE  graduation_date IS NOT NULL;
   Active_Student_rec         Active_Students_cur%ROWTYPE;
   vCurrentSSN                STUDENTS.ssn%ROWTYPE;
   nNewGPA                    STUDENTS.gpa%TYPE;
   FOR Active_Student_rec IN Active_Student_cur LOOP
       nNewGPA := Calculate_GPA (vSSN => vCurrentSSN);
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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter