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

I know this answer is kind of late but just want to let you know that you can also use:

ALTER PACKAGE your_package_name_here COMPILE PACKAGE;

ALTER PACKAGE your_package_name_here COMPILE BODY;

then if warning was shown, you can use the below script to check the error and which lines it resides in:

-- this shows the errors within the package itself

SHOW ERRORS PACKAGE your_package_name_here;

-- this shows the errors within the package body

SHOW ERRORS PACKAGE BODY your_package_name_here;

Answer 2

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';