Handling PL/SQL Compile-Time Errors

When you use SQL*Plus to submit PL/SQL code, and when the code contains errors, you receive notification that compilation errors have occurred, but there is no immediate indication of what the errors are. For example, if you submit a standalone (or stored) procedure PROC1 in the file proc1.sql as follows:

SQL> @proc1

And, if there are one or more errors in the code, then you receive a notice such as the following:

MGR-00072: Warning: Procedure proc1 created with compilation errors

In this case, use the SHOW ERRORS statement in SQL*Plus to get a list of the errors that were found. SHOW ERRORS with no argument lists the errors from the most recent compilation. You can qualify SHOW ERRORS using the name of a procedure, function, package, or package body:

SQL> SHOW ERRORS PROC1
SQL> SHOW ERRORS PROCEDURE PROC1

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

Assume that you want to create a simple procedure that deletes records from the employee table using SQL*Plus:

CREATE OR REPLACE PROCEDURE Fire_emp(Emp_id NUMBER) AS
   BEGIN
      DELETE FROM Emp_tab WHER Empno = Emp_id;
   END
/

Notice that the CREATE PROCEDURE statement has two errors: the DELETE statement has an error (the E is absent from WHERE), and the semicolon is missing after END.

After the CREATE PROCEDURE statement is entered and an error is returned, a SHOW ERRORS statement returns the following lines:

SHOW ERRORS;

ERRORS FOR PROCEDURE Fire_emp:
LINE/COL       ERROR
-------------- --------------------------------------------
3/27           PL/SQL-00103: Encountered the symbol "EMPNO" wh. . .
5/0            PL/SQL-00103: Encountered the symbol "END" when . . .
2 rows selected.

Notice that each line and column number where errors were found is listed by the SHOW ERRORS statement.

Alternatively, you can query the following data dictionary views to list errors when using any tool or application:

USER_ERRORS
ALL_ERRORS
DBA_ERRORS

The error text associated with the compilation of a procedure is updated when the procedure is replaced, and it is deleted when the procedure is dropped.

Original source code can be retrieved from the data dictionary using the following views: ALL_SOURCE, USER_SOURCE, and DBA_SOURCE.

< < Cursor Variables

Handling Run-Time PL/SQL Errors >>