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 Line Numbers
Oracle Tips by Burleson
 

Notice that each error is reported with a line number and a position number. SQL*Plus skips over blank lines when compiling code, so you’ll need to determine the line of code to which the line number refers. This is done by using the list command, as shown in Figure 8.4.

Figure 8.4  Using the list command to find a line of code.

If you like, you can also use the list command to display a range of lines. For example,

list 10 15

displays lines 10 through 15 of your source code. If you specify just one line number, list will only display that line. For instance,

list 10

displays only line 10 of your code. Using the list command without specifying a line number instructs SQL*Plus to display the entire contents of the buffer.

When The Line Number Is Wrong

Oracle reports the line number on which an error is detected. It’s not uncommon for the reported line number to be incorrect, because you’ve done something else incorrectly in your code that has no effect until Oracle tries to compile the line number specified in the output of the show errors command.

Most of the time, incorrect line numbers are the result of variable and type declaration problems, or as a result of incorrect references to objects or variables. Consider again the code for the Calculate_Student_Grades() procedure, presented in Listing 8.4.

Listing 8.4 The 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%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;
/

Attempting to compile this code generates three errors. The line and position numbers of these errors are shown in Table 8.1.

Table 8.1 Compile errors for the Calculate_Student_Grades() procedure.

Line Number

Position

Error

9

31

PLS-00310: with %ROWTYPE attribute, ‘STUDENTS.SSN’ must name a table, cursor or cursor-variable

10

40

PLS-00302: component ‘GPA’ must be declared

12

30

PLS-00201: identifier ‘ACTIVE_STUDENT_CUR’ must be declared

Running the list command against line 12 of the code for the procedure shows that the error occurred at the BEGIN statement. This isn’t really the case. The real cause of the error is the reference on line 13 to the cursor Active_Student_cur.

Taking a good look at the variable declarations section makes it pretty clear that line 13 should reference the cursor Active_Student_cur. In this instance, debugging by following the line number is a dead end.

As frustrating as compile errors can sometimes be, debugging runtime errors is more frustrating.


               
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