Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

cannot compile body of dbms_utility without its specification

IngenioSep 27 2012 — edited Sep 27 2012
hi there!
I just got this error on my 11g EE database

yesterday had power problems, and the backup batery did not work(but it happens very often)

today, trying to send email with a job, i realized through TOAD that sys has 304 invalid objects.
Try to compile and it seems something missing.

"cannot compile body of dbms_utility without its specification"

I cant find that specification and no forums found related to this.

need help from experts..

S.O. Oracle enterprise linux 4.7
D.B. Oracle Enterprise 11.2

thanks!
This post has been answered by Virendra.K1.Yadav on Sep 27 2012
Jump to Answer

Comments

Virendra.K1.Yadav
Can you please post output of below command: 
select owner,object_type,last_ddl_time,status from dba_objects t where t.object_name='DBMS_UTILITY';
In worst case you can run catalog.sql and catproc.sql to recreate objects in SYS and then run utlrp.sql to recompile them all.

Regards,
Virendra

Edited by: 948516 on Sep 27, 2012 11:56 AM
Ingenio
this is the output,

SYS,PACKAGE,27/09/2012 11:59:25 a.m.,INVALID,
SYS,PACKAGE BODY,27/09/2012 12:02:42 p.m.,INVALID,
PUBLIC,SYNONYM,27/09/2012 11:59:38 a.m.,INVALID,

do I still need to try running catalog,, catproc and utlrp ?

thanks!
Virendra.K1.Yadav
You can connect as SYS and try following: 
ALTER PACKAGE SYS.DBMS_UTILITY COMPILE;
show errors
ALTER PACKAGE SYS.DBMS_UTILITY COMPILE BODY;
show errors
Please post the output here.

Regards,
Virendra
Ingenio
Virendra, thanks for your time, this is the output

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER PACKAGE SYS.DBMS_UTILITY COMPILE;
show errors
ALTER PACKAGE SYS.DBMS_UTILITY COMPILE BODY;
show errors
Warning: Package altered with compilation errors.

SQL> Errors for PACKAGE SYS.DBMS_UTILITY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
262/75 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
end function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

SQL>
Warning: Package Body altered with compilation errors.

SQL>
Virendra.K1.Yadav
Answer
It looks like you cannot compile this.
Can you try runing below on the server where database is installed. This may take a while to complete: 
SQL>@?/rdbms/admin/catalog.sql
once this is done please check the number of invalids run below and take invalids again 
SQL>@?/rdbms/admin/utlrp.sql
if this solves then okay, else please run below 
SQL>@?/rdbms/admin/catproc.sql
and 
SQL>@?/rdbms/admin/utlrp.sql
This should solve you problem.

Regards,
Virendra
Marked as Answer by Ingenio · Sep 27 2020
Ingenio
that resolved my problems.
New things learned everyday.

thank you!
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 25 2012
Added on Sep 27 2012
#general-database-discussions
6 comments
984 views