 |
|
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.
|
