One of the really annoying things about Oracle PL/SQL is the way it tells you something is wrong, gives you a line number and then leaves it up to you to find the statement that caused the error. Well, this little piece of SQL gives you the exact line in error plus the lines immediately before and after it. (Acknowledgements to Ken Atkins of ARI…

plsqlerrors.sql

set verify off
define obj_name = '&1';
column outline format a105 heading 'Error Listing';
break on err_text skip 2;
set linesize 105;
set pagesize 0;
set pause off;
spool listerr
SELECT 
     decode(to_char(us.line), to_char(ue.line-7),ue.text,
                              to_char(ue.line-6),'',
                              to_char(ue.line+6),'',
                              to_char(ue.line)  ,
                              '   --'||to_char(us.line,'99990')||' '||us.text,
                              ''||to_char(us.line,'99990')||''||us.text) outline
     from user_source us, user_errors ue
      where us.name = '&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: Statement ignored'
/

     spool off
     set pagesize 22;