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




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.

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%TYPE;
   nNewGPA                    STUDENTS.overall_gpa%TYPE;
   FOR Active_Student_rec IN Active_Students_cur LOOP
       vCurrentSSN := Active_Student_rec.ssn;
       nNewGPA := Calculate_GPA (vSSN => vCurrentSSN);
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
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.


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