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?

Exeat

Answer 1

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.

Exeat

Answer 2

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;

Ileostomy

This is the best answer. – Frigate

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

Whiteside

Answer 4

try

SHOW ERRORS PACKAGE BODY FLOWS_020000.WWV_FLOW_QUERY

Ocieock

Answer 5

SHOW ERRORS PACKAGE FLOWS_020000.WWV_FLOW_QUERY

Fundus

Answer 6

I had same issue while compiling Oracle system objects which were in invalid state. In your database PUBLIC user is missing or revoked privileges.

Connect as sys user

SQL> ALTER PACKAGE OWNER.PACKAGE COMPILE BODY;

Warning: Package Body altered with compilation errors.

As you get the above warning, Do this.

SQL> show error
Errors for PACKAGE BODY MDSYS.SDO_GEOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
939/3    PL/SQL: Statement ignored
939/3    PLS-00201: identifier 'DBMS_LOB' must be declared
6959/3   PL/SQL: Statement ignored
6959/3   PLS-00201: identifier 'DBMS_LOB' must be declared
7072/3   PL/SQL: Statement ignored
7072/3   PLS-00201: identifier 'DBMS_LOB' must be declared
7708/5   PL/SQL: Statement ignored
7708/5   PLS-00201: identifier 'DBMS_LOB' must be declared

This will list the errors. In my case, PUBLIC was missing privileges on DBMS_LOB.

I granted the same and was able to compile the package.

SQL> grant execute on dbms_lob to PUBLIC;

Grant succeeded.


SQL> alter package MDSYS.SDO_GEOR compile body;

Package body altered.

Hope this helps you!

Prismatic

This answer doesn't seem related to the question asked, it's a different problem – Fordo

Answer 7

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.

Leonardo