Handling PL/SQL Compile-Time Errors

To list compile-time errors, query the static data dictionary view *_ERRORS. From these views, you can retrieve original source code. The error text associated with the compilation of a subprogram is updated when the subprogram is replaced, and it is deleted when the subprogram is dropped.

SQL*Plus issues a warning message for compile-time errors, but for more information about them, you must use the command SHOW ERRORS.

Note:

Before issuing the SHOW ERRORS statement, use the SET LINESIZE statement to get long lines on output. The value 132 is usually a good choice. For example:

SET LINESIZE 132

Example: Compile-Time Errors has two compile-time errors: WHER should be WHERE, and END should be followed by a semicolon. SHOW ERRORS shows the line, column, and description of each error.

Compile-Time Errors

CREATE OR REPLACE PROCEDURE fire_emp (
  emp_id NUMBER
) AS
BEGIN
  DELETE FROM EMPLOYEES
  WHER EMPLOYEE_ID = Emp_id;
END
/

Result:

Warning: Procedure created with compilation errors.

Command:

SHOW ERRORS;

Result:

Errors for PROCEDURE FIRE_EMP:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
6/8    PL/SQL: ORA-00933: SQL command not properly ended
7/3    PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         ; <an identifier> <a double-quoted delimited-identifier>
         current delete exists prior <a single-quoted SQL string>
         The symbol ";" was substituted for "end-of-file" to continue.