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


 

HTML Text

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.

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.

  
 

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