Oracle Function/Procedure/Package 调试方法

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

See Also:
SQL*Plus User's Guide and Reference for complete information about the SHOW ERRORS statement

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.

SHOW ERRORS在调试Create Function/ Procedure/ Package时非常有用, 让我找了好几天, 要不错了就一个"MGR-00072: Warning: Procedure proc1 created with compilation errors"很是郁闷, 自己也不知道错在什么地方了!

Ref: Oracle 10.2 Document