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 ‘%’:
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 (
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
distinct -1 n,owner,name,type,line
,type||' '||owner||'.'||name||' line '||line text
-- 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)
-- error message with indication of the position in the line
select sequence n,owner,name,type,line
,lpad(' ',position-1,' ')||'^'||text text
) order by owner,name,type,line,n;
I’ll be happy to have your feedback on Twitter: https://twitter.com/FranckPachot/status/1106246663096713222