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.sql—The 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.sql—Show 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