PL/SQL compilation fails with no error message

Question

My installation of APEX has come pear shaped on a Oracle 9.2.0.5.0 instance, all the packages are invalid.

I've tried recompiling everything with DBMS_UTILITY.compile_schema, but still all the packages are invalid. So, tried recompiling individual packages,

SQL> ALTER PACKAGE FLOWS_020000.WWV_FLOW_QUERY COMPILE BODY;

Warning: Package Body altered with compilation errors.

SQL> show err
No errors.
SQL> 
SQL> ALTER PACKAGE FLOWS_020000.WWV_FLOW_QUERY COMPILE;

Warning: Package altered with compilation errors.

SQL> show err
No errors.
SQL>

nothing in the alter log for it..

How can I find what the error is? shouldn't "show err" give it to me?

Answer 1

try

SHOW ERRORS PACKAGE BODY FLOWS_020000.WWV_FLOW_QUERY

Answer 2

SHOW ERRORS PACKAGE FLOWS_020000.WWV_FLOW_QUERY

Answer 3

Conn as FLOWS_020000 and go:

SELECT *
FROM   ALL_ERRORS
WHERE  OWNER = USER;

Or conn as SYSTEM and go

SELECT *
FROM   ALL_ERRORS
WHERE  OWNER = 'FLOWS_020000';

Answer 4

After giving up on this problem for a few months, then coming back to it, I worked it out.

The package I was trying to compile had been wrapped. Apparently, you when compiling wrapped packages, you must be logged in as the package owner.

I suspect a bug was also at play here, as some deeper investigation showed when compiling not as the owner, the server process was actually running out of memory and dying, but logging in as the package owner allowed the object to be compiled without issue.

Answer 5

I had the same issue today. I found that I was doing (as XXX):

alter package XXX.my_package compile body;

It would error, and then a show err would not actually show any error.

Removing the 'XXX.' allowed me to see the errors.