Join with ALL_SOURCE
ALL_ERRORS can be joined with
ALL_SOURCE so that PL/SQL source lines causing errors are displayed side by side with the error text:
select
err.owner,
err.name err,
-- cast(err.name as varchar2(50)) name,
-- cast(substr(err.text, 1, 100) as varchar2(100)) err_text,
regexp_replace(err.text, chr(10) || '.*', '', 1, 1, 'mni') err_text_no_new_line,
-- err.text err_text,
-------------------
case
when err.text like 'PL/SQL: ORA-04063:%' then
regexp_replace(err.text, 'PL/SQL: ORA-04063: view "([^"]+)" has errors' , '\1')
when err.text like 'PLS-00201: identifier%' then
regexp_replace(err.text, 'PLS-00201: identifier ''([^'']+)'' must be declared', '\1')
when err.text like 'PLS-00905:%' then
regexp_replace(err.text, 'PLS-00905: object (\S+) is invalid', '\1')
when err.text like 'ORA-00904:%' then
regexp_replace(err.text, 'ORA-00904: ([^:]+): invalid identifier', '\1')
when err.text like'%ORA-00942:%' then
regexp_replace(substr(src.text, err.position), '([a-z0-9._$#]+).*', '\1', 1, 1, 'i')
end identifier,
-------------------
src.text src_text,
-- err.message_number msg#,
-- cast(substr(src.text, 1, 100) as varchar2(100)) src_text
err.type plsql_type,
err.line,
err.position
from
all_errors err left join
all_source src on err.name = src.name and
err.type = src.type and
err.line = src.line and
err.owner = src.owner
where
-- err.text like '%00904%' and
err.text != 'PL/SQL: SQL Statement ignored' and
err.text != 'PL/SQL: Statement ignored' and
err.text not like 'PLW%' and -- <== Don't select PL/SQL warnings
-----------------------------------------------------
err.owner = user and
substr(err.name, 1, 3) != 'SH_'
order by
err.owner,
err.name,
err.type,
err.line
;
Most recent errors
The following select statement joins
dba_errors with
dba_objects to order the result set by the most recently modified objects first (
order by timestamp desc):
select
err.owner,
err.name,
obj.last_ddl_time,
err.text,
err.attribute,
err.type,
err.line,
err.position,
err.message_number
from
dba_errors err join
dba_objects obj on err.owner = obj.owner and
err.name = obj.object_name and
err.type = obj.object_type
-- where
-- err.owner = user
-- obj.object_name ='FOO'
order by
obj.timestamp desc nulls last,
err.sequence;