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 stand-alone (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
|
Before issuing the SET CHARWIDTH 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.