by Granville Bonyata on October 22, 2013

Inexplicably getting PLS-00907: cannot load library unit SCHEMA_NAME.TABLE_NAME (referenced by SCHEMA_NAME.PACKAGE_NAME)?

Oracle’s PLS-00907 can be caused by several issues, but one is, fortunately, easy to fix. It has to do with corruption in the dependencies table, where the timestamp in the dependency table does not match the object’s timestamp in sys.obj$.

If you get a PLS-00907 try running this query (which I found on the Oracle forums):

select do.obj# d_obj, d_name, do.type# d_type, po.obj# p_obj, p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+) and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;

Then simply do an alter compile on each dependent object until this query comes up clean.

Previous post:

Next post: