 |
|
Fixing Compile Error
Oracle Tips by
Burleson
|
Now that the compilation errors for the Calculate_Student_Grades()
procedure have been identified, the source code has to be modified to
fix the errors so the procedure can be recompiled. The revised source
code that fixes these compile errors is shown in Listing 8.2.
Listing 8.2 The revised 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%TYPE;
nNewGPA STUDENTS.overall_gpa%TYPE;
BEGIN
FOR Active_Student_rec IN Active_Students_cur LOOP
vCurrentSSN := Active_Student_rec.ssn;
nNewGPA := Calculate_GPA (vSSN => vCurrentSSN);
END LOOP;
END Calculate_Student_Grades;
/
Figure 8.2 illustrates what happens when we attempt to recompile
the procedure now.
Figure 8.2 Compiling the Calculate_Student_Grades()
procedure after fixing compile errors.
As you can see, there is still at least one compile error in the
source code. Once again, we need to issue the show errors
command to see which compilation errors have occurred. The result of
the show errors command is shown in Figure 8.3.
Figure 8.3 Compile errors in the Calculate_Student_Grades()
procedure.
Oops! It looks like the Calculate_GPA() function doesn’t
exist. Resolving this compile error requires finding out what happened
to the function. Is it in another schema? Does the function even
exist?
This process is repeated as necessary to obtain a clean compile for
the procedure.
Using Data Dictionary Tables
Errors displayed as a result of using the show errors
command don’t appear out of nowhere. These errors are stored in the
ALL_ERRORS view, which has the following structure:
owner varchar2 (30)
name varchar2 (30)
type varchar2 (12)
sequence number
line number
position number
text varchar2 (2000)
Since most developers debug one block of code and then move on to
another, there’s seldom a need to query this table. If you have
several blocks of code that all have compile errors, you can query the
errors for each object individually from this view using a query like
the one shown in Listing 8.3.
Listing 8.3 Pulling error information from the ALL_ERRORS
view.
SELECT line, position, text
FROM ALL_ERRORS
WHERE owner = upper ('&1')
AND name = upper ('&2')
AND type = upper ('&3')
ORDER BY line, position;
To use the preceding query, replace &1 with the schema name
that owns the object, &2 with the name of the object, and &3
with the type of the object. This query will return the text and
position of the error, including a line number.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
|
