255-261

Previous Table of Contents Next


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.

Line Numbers

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.

Runtime Errors

Runtime errors are errors that occur while code is executing. These errors can arise due to data problems or code problems. For instance, attempting to assign a 31-character string to a varchar2 (30) variable will cause a runtime error.

Most approaches to dealing with runtime errors utilize the DBMS_Output package to isolate the location of an error so that a developer can correct the problem. This package provides an excellent debugging tool when used properly.


Previous Table of Contents Next


High Performance Oracle Database Automation
High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL
ISBN: 1576101525
EAN: 2147483647
Year: 2005
Pages: 92

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net