Oracle: SHOW ERRORS with the source lines
Join dba_errors and dba_source to display PL/SQL compilation errors with the offending code.
SHOW ERRORS in SQL*Plus lists compilation errors without the faulty source. Joining dba_errors and dba_source shows each error together with the offending source line and a caret under the error position.
Query
set lines 100
set pages 100
column text format a100
with err as (
select distinct
owner,
name,
type,
line,
position,
sequence,
text
from
dba_errors
where
sequence=1
)
select
decode(n,-1,'* ',' ')||text text from (
select
sequence n,
owner,name,
type,line,
lpad(' ',position-1,' ')||'^'||text text
from
err
union all
select distinct
-1 n,
owner,
name,
type,
line,
type||' '||owner||'.'||name||' line '||line
from
err
union all
select
0,
owner,
name,
type,
line,
text
from
dba_source
where
(owner,name,type,line) in
(select
owner,
name,
type,
line
from
err)
order by
owner,name,
type,line,
n
);
Notes
- Only the first error of each object (
sequence=1) is shown.