Oracle stored procedure compilation errors displayed for humans

Here is a script I use a lot especially when importing a schema with Data Pump and checking for invalid objects. I usually don’t care about compilation errors at compile time but just run UTL_RECOMP.RECOMP_PARALLEL at the end and check for errors on invalid objects. Here is an example.

I have imported a schema with Data pump and got some compilation errors:

I want to resolve them, or at least to understand them.

If I query DBA_ERRORS, I get the following:

This is a small example, but it can be huge. Not very helpful:

• I have some error messages like “PL/SQL: ORA-00942: table or view does not exist” but with no mention of wich table. I have to go to the source with line and position.
• I have some other errors which mention an object which is invalid, but the reason may be a consequence of the previous one. Then I want to see only the previous one.

This is why I use the following query to filter only top-level errors, lokking at DBA_DEPENDENCY to get the first ones, and show the line from DBA_SOURCE in order to give a better idea.

Here is my output for the same errors:

I can see clearly that I have only one object in error, which has a SELECT statement ignored because the table ELEMENT_INFO is not visible. No need to dig into the code, just checking why this table is not there.

Here is the script, just mention the schema name instead of ‘%’:

with 
 schemas as (select username owner from dba_users where 
 username like '%'
),
errors as (
 select * from dba_errors natural join schemas
),
top_errors as (
 -- only top errors in dependency
 select * from errors
 where (owner,name,type) not in (
  select owner,name,type 
  from dba_dependencies 
   where (referenced_owner,referenced_name,referenced_type) 
   in (select owner,name,type from errors)
  )
 )
-- here is the select to join with dba_source
select /*+ first_rows(1) */ decode(n,-1,'* ','  ')||text text from (
 -- name/type of the object in error
 select
 distinct -1       n,owner,name,type,line
 ,type||' '||owner||'.'||name||' line '||line text
 from top_errors
 union all
 -- line with error (from dba source)
 select  0       n,owner,name,type,line,text
 from dba_source where (owner,name,type,line)
  in ( select owner,name,type,line from top_errors)
 union all
 -- error message with indication of the position in the line
 select sequence n,owner,name,type,line
 ,lpad(' ',position-1,' ')||'^'||text text
 from top_errors 
) order by owner,name,type,line,n;

I’ll be happy to have your feedback on Twitter: https://twitter.com/FranckPachot/status/1106246663096713222