Page 250
The server sends back:
Warning: Procedure created with compilation errors.
You type:
SHOW ERRORS
And this is what you see:
Errors for PROCEDURE SHOWERR1: LINE/COL ERROR -------- -------------------------------------------------------------- 9/18 PLS-00206: %TYPE must be applied to a variable or column, not `GET_ERRORS' 9/18 PL/SQL: Item ignored 19/5 PL/SQL: SQL Statement ignored 19/27 PLS-00320: the declaration of the type of this expression is incomplete or malformed 21/5 PL/SQL: Statement ignored 21/45 PLS-00320: the declaration of the type of this expression is incomplete or malformed 23/5 PL/SQL: Statement ignored 23/26 PLS-00320: the declaration of the type of this expression is incomplete or malformed 26/3 PL/SQL: Statement ignored 26/52 PLS-00201: identifier `ERRS' must be declared
NOTE |
The PL/SQL engine strips blank lines from the stored source code. If you sprinkle double spacing throughout your programs for readability, you will discover that the source code stored in USER_SOURCE will quickly become unsynchronized from your source module, making debugging from the original source code file more difficult. |
Some of these error messages are fairly informative; others are quite vague. The line number and column where the error occurred are also provided. Unfortunately, the source code line suffering the error is not displayed. You can display the source code with this SQL statement:
SELECT line, text FROM user_source WHERE name='SHOWERR1' AND line IN (9, 19, 21, 23, 26) ORDER BY line;
The source lines displayed are
LINE TEXT ---- -------------------------------------------------------------- > 9 get_errors_rec get_errors%TYPE; 19 FETCH get_errors INTO get_errors_rec; 21 DBMS_OUTPUT.put_line(`At Line/Col: ` || get_errors_rec.line || 23 DBMS_OUTPUT.put_line(get_errors_rec.text); 26 DBMS_OUTPUT.put_line(`Errors Found: ` || TO_CHAR(errs));
Page 251
Matching up the error messages above, you see that:
Line 9, you have to base the record variable on
get_errors%ROWTYPE, not %TYPE.
Line 19 failed because get_errors_rec was not defined correctly.
Line 21 failed because get_errors_rec was not defined correctly.
Line 23 failed because get_errors_rec was not defined correctly.
Line 26, you forgot to declare the variable
errs.
You can see how an error can propagate through the code. Fix it in the declaration and the related errors go away.
Also notice that some syntax errors were not uncovered during this compilation. For instance, the column named `type' needs to be capitalized and double quoted because TYPE is actually a reserved word. But once the five errors above are cleaned up, this bug appears (try it).
The repaired stored procedure is found in Listing 11.10 (with a couple of added features).
Listing 11.10 showerrs.sqlThe Corrected Show Errors Stored Procedure
CREATE OR REPLACE PROCEDURE showerrs( Pname IN user_errors.name%TYPE, Ptype IN user_errors."TYPE"%TYPE DEFAULT NULL) IS CURSOR get_errors(Cname IN user_errors.name%TYPE, Ctype IN user_errors."TYPE"%TYPE) IS SELECT * FROM USER_ERRORS WHERE name = Cname AND "TYPE" = Ctype ORDER BY SEQUENCE; get_errors_rec get_errors%ROWTYPE; status NUMERIC := 0; Lname user_errors.name%TYPE; Ltype user_errors.type%TYPE; errs NUMERIC := 0; -- number of errors BEGIN Lname := UPPER(Pname); IF (Ptype IS NOT NULL) THEN -- user supplied type Ltype := UPPER(Ptype); ELSE -- look for 1 object and get the type BEGIN SELECT object_type INTO Ltype FROM user_objects WHERE object_name = Lname; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20100, Lname || ` type ambiguously defined'); END; END IF; -- user supplied type DBMS_OUTPUT.put_line(`Compilation errors for ` || Ltype || ` ` || Lname); OPEN get_errors(Lname, Ltype); LOOP -- display all errors for this object
Page 252
Listing 11.10 Continued
FETCH get_errors INTO get_errors_rec; EXIT WHEN get_errors%NOTFOUND; DBMS_OUTPUT.put_line(`At Line/Col: ` || TO_CHAR(get_errors_rec.line) || `/' || TO_CHAR(get_errors_rec.position)); DBMS_OUTPUT.put_line(get_errors_rec.text); errs := errs + 1; END LOOP; -- display all errors CLOSE get_errors; DBMS_OUTPUT.put_line(`Errors Found: ` || TO_CHAR(errs)); EXCEPTION WHEN OTHERS THEN BEGIN status := SQLCODE; IF (get_errors%ISOPEN) THEN -- cursor still open CLOSE get_errors; END IF; DBMS_OUTPUT.put_line(`showerrs: ` || SQLERRM(status)); EXCEPTION WHEN OTHERS THEN NULL; -- don't care END; END showerrs; /.
If you run this stored procedure with:
execute showerrs(`showerr1');
you'll get:
Compilation errors for PROCEDURE SHOWERR1 At Line/Col: 9/18 PLS-00206: %TYPE must be applied to a variable or column, not `GET_ERRORS' At Line/Col: 9/18 PL/SQL: Item ignored At Line/Col: 19/27 PLS-00320: the declaration of the type of this expression is incomplete or malformed At Line/Col: 19/5 PL/SQL: SQL Statement ignored At Line/Col: 21/45 PLS-00320: the declaration of the type of this expression is incomplete or malformed At Line/Col: 21/5 PL/SQL: Statement ignored At Line/Col: 23/26 PLS-00320: the declaration of the type of this expression is incomplete or malformed At Line/Col: 23/5 PL/SQL: Statement ignored At Line/Col: 26/52
Page 253
PLS-00201: identifier `ERRS' must be declared At Line/Col: 26/3 PL/SQL: Statement ignored Errors Found: 10 PL/SQL procedure successfully completed.
This is about as useful as SHOW ERRORS. A big improvement would be to show the line number and point out the column where the error occurred. Let's do this by combining USER_SOURCE into the above program (see Listing 11.11). I've also prettied it up by removing the trailing newline from the source line and adding double-spacing between errors. I've also foregone displaying the derivative errors that start with PL/SQL, which only serves to indicate that a multi-line statement has errors.
Listing 11.11 showerr.sqlShow Errors with Offending Source Code
CREATE OR REPLACE PROCEDURE showerr( Pname IN user_errors.name%TYPE, Ptype IN user_errors."TYPE"%TYPE DEFAULT NULL) IS CURSOR get_errors(Cname IN user_errors.name%TYPE, Ctype IN user_errors."TYPE"%TYPE) IS SELECT E.sequence, E.line, E.position, E.text err_text, S.text src_text FROM USER_ERRORS E, USER_SOURCE S WHERE E.name = Cname AND E."TYPE" = Ctype AND S.name = E.name AND S."TYPE" = E."TYPE" AND S.line = E.line ORDER BY E.sequence; get_errors_rec get_errors%ROWTYPE; status NUMERIC := 0; Lname user_errors.name%TYPE; Ltype user_errors.type%TYPE; errs NUMERIC := 0; -- number of errors cols NUMERIC; -- extra column padding needed for error position wspc VARCHAR2(2) := CHR(20) || CHR(10); -- trailing whitespace dspc VARCHAR2(1) := CHR(9); -- double spacing BEGIN Lname := UPPER(Pname); IF (Ptype IS NOT NULL) THEN -- user supplied type Ltype := UPPER(Ptype); ELSE -- look for 1 object and get the type BEGIN SELECT object_type INTO Ltype FROM user_objects WHERE object_name = Lname; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20100, Lname || ` type ambiguously defined'); END; END IF; -- user supplied type