Previous | Table of Contents | Next

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

Previous | Table of Contents | Next