REM
/*
 * PLSQLErr.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */


set verify off
define obj_name = '&1';
Column Line_no NoPrint
break on err_text skip 2;
set linesize 105 pagesize 0 pause off feedback off
SELECT	 ue.line Line_no
	,us.line Line_no
     ,decode(to_char(us.line), to_char(ue.line-7),ue.text||' [at line '||ue.line||', position '||ue.position||']',
                              to_char(ue.line-6),'',
                              to_char(ue.line+6),'',
                              to_char(ue.line)  ,
                              '-->'||to_char(us.line,'99990')||' '||TRIM(us.text),
--LTRIM removes leading TABs which confuses the the "finger" below
--SUBSTR(us.text,1,position-1)||'{'||SUBSTR(us.text,position,1)||'}'||SUBSTR(us.text,position+1),
                              '   '||to_char(us.line,'99990')||' '||us.text) 
	outline
     from user_source us, user_errors ue
      where us.name = UPPER('&obj_name')
     and us.line between (ue.line-7) and (ue.line+6)
     and us.name = ue.name
     and us.type = ue.type
     -- This predicate is put here to elminate this useless fallout error
     and ue.text != 'PL/SQL: SQL Statement ignored'
     and ue.text != 'PL/SQL: Statement ignored'
     and ue.text != 'PL/SQL: Item ignored'
union all
select	ue.line Line_no,ue.line Line_no,
	RPAD(' ',LEAST((position-1),128)+LENGTH('-->'||to_char(ue.line,'99990')))||'^'
from	user_errors ue
WHERE	ue.name = UPPER('&obj_name')
AND	ue.text != 'PL/SQL: SQL Statement ignored'
and	ue.text != 'PL/SQL: Item ignored'
and	ue.text != 'PL/SQL: Statement ignored'
Order BY 1,2
/

clear columns

SET DOC off
/* SUMMARY - Show the source code for compilation errors of a package or procedure, with a "window" of 5 lines before and after each error line and a "pointer" to each error location.
 *
 * DETAIL - There are a lot of good GUI tools to help you develop PL/SQL code, which will highlight and assist in fixing compilation errors.
 *          However, they tend to be rather heavyweight and if you are not a developer (ie you are a DBA) then you may not have access to one
 *          or not have one readily to hand. You can see the compilation errors via the SHOW ERRORS command but the format is rather lacking and
 *          it can be hard to spot where the error actually lies using that command from within SQL*Plus.
 *          This little script gets the error text out of the USER_ERRORS view and combines that with the USER_SOURCE view to not only show each line that 
 *          has an error, "points" to the statement within the error line and gives you contextual information by displaying the 5 lines before and after
 *          each.
 *          Whilst possibly not as slick as a GUI product it is fast (very fast) and extremely easy to use. Simply run the script using the name of the package
 *          that filed to compile as the only parameter.
 *
 *          Sample output from using this script might look like (with the basic SHOW ERRORS: error details included for comparison):
 *
SQL> ALTER PACKAGE mybadpackage COMPILE;

Package compiled with errors.

SQL> @plsqlerr mybadpackage

PL/SQL: Declaration ignored [at line 17, position 1]

       12 -- Sub-Program Unit Declarations
       13 /* call to ensure package is loaded in shared pool */
       14 PROCEDURE LOAD_INTO_POOL;
       15
       16 /* mybadprocedure */
-->    17 PROCEDURE mybadprocedure
         ^
       18  (id          IN NUMBER
       19  ,DESCRIPTION IN mytable.description%TYPE
       20  ,COMPONENT   IN VARCHAR2
       21  ,ERROR_LEVEL IN PLS_INTEGER
       22  ,CREATED_BY  IN VARCHAR2

PLS-00201: identifier 'mytable.description' must be declared [at line 19, position 20]

       14 PROCEDURE LOAD_INTO_POOL;
       15
       16 /* mybadprocedure  */
       17 PROCEDURE mybadprocedure 
       18  (id          IN NUMBER
-->    19 ,DESCRIPTION IN mytable.description%TYPE
                            ^
       20  ,COMPONENT   IN VARCHAR2
       21  ,ERROR_LEVEL IN PLS_INTEGER
       22  ,CREATED_BY  IN VARCHAR2
       23  );
       24

SQL> SHOW ERRORS
Errors for PACKAGE mybadpackage:
17/1
PL/SQL: Declaration ignored

19/20
PLS-00201: identifier 'mytable.description' must be declared

SQL>
 * (c) Tim Onions 2007
 * Script used at the database owners risk.
 */
REM