DBMS_UTILITY.COMPILE_SCHEMA(schema => '<SCHEMA_NAME>', compile_all => FALSE) doesn't compile invalid package bodies

Question

The call of

BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(schema => '<SCHEMA_NAME>', compile_all => FALSE);
END;
/

doesn't compile invalid package bodies. Does anybody know the reason?

(Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production)

Answer 1

It does; at least in 11.2.0.3, so I suppose there could have been a bug in the base release.

if I create an invalid package, in this case because it refers to a table that doesn't exist:

create package p42 as
  procedure test;
end p42;
/

PACKAGE P42 compiled

create package body p42 as
  procedure test is
    n number;
  begin
    select count(*) into n from t42;
  end test;
end p42;
/

PACKAGE BODY P42 compiled
Errors: check compiler log

And then check the status and last DDL time:

select object_type, object_name, status, last_ddl_time
from user_objects where object_name = 'P42'
order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME          STATUS  LAST_DDL_TIME      
------------------- -------------------- ------- -------------------
PACKAGE             P42                  VALID   2015-03-02 17:39:42 
PACKAGE BODY        P42                  INVALID 2015-03-02 17:39:42 

And then recompile the schema and check again:

BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);
END;
/

anonymous block completed

select object_type, object_name, status, last_ddl_time
from user_objects where object_name = 'P42'
order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME          STATUS  LAST_DDL_TIME      
------------------- -------------------- ------- -------------------
PACKAGE             P42                  VALID   2015-03-02 17:39:42 
PACKAGE BODY        P42                  INVALID 2015-03-02 17:39:49 

.. the last DDL time has changed, so it was recompiled. It's still invalid as I haven't fixed the underlying problem. And I can see the

select text from user_errors where name = 'P42';

TEXT                                                        
------------------------------------------------------------
PL/SQL: ORA-00942: table or view does not exist              
PL/SQL: SQL Statement ignored                                

Or if your '<SCHEMA_NAME>' isn't your current user, it will be in all_errors, if it's still invalid.

If I create the missing table and compile the schema again:

create table t42 (id number);

Table t42 created.

BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);
END;
/

anonymous block completed

select object_type, object_name, status, last_ddl_time
from user_objects where object_name = 'P42'
order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME          STATUS  LAST_DDL_TIME      
------------------- -------------------- ------- -------------------
PACKAGE             P42                  VALID   2015-03-02 17:39:42 
PACKAGE BODY        P42                  VALID   2015-03-02 17:40:11 

... the last DDL time has changed again, as has the status now. If I compile again, with your compile_all => FALSE flag, then the last DDL time won't change as it won't look at the valid package.