Search

Top 60 Oracle Blogs

Recent comments

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