OraFAQ Forum: SQL & PL/SQL » Procedure altered with compilation errors

Home » SQL & PL/SQL » SQL & PL/SQL » Procedure altered with compilation errors (11.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Procedure altered with compilation errors [message #636014]

Tue, 14 April 2015 12:35

krish96
Messages: 74
Registered: October 2012

Member

Hello Experts,

I have upgraded from 11.2.01 to 11.2.04 in-place on windows server and using datapump .. after importing the data into the database there are 200 procedures invalid objects, and when i do re-compile using utlrp.sql and compile manually each object, and it gives the same errors ..

""Warning: Procedure altered with compilation errors.""

Please suggest me with your thoughts.. how to fix this issue..

Thanks in advance..

Report message to a moderator

Re: Procedure altered with compilation errors [message #636015 is a reply to message #636014]

Tue, 14 April 2015 12:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

krish96 wrote on Tue, 14 April 2015 10:35

Hello Experts,

I have upgraded from 11.2.01 to 11.2.04 in-place on windows server and using datapump .. after importing the data into the database there are 200 procedures invalid objects, and when i do re-compile using utlrp.sql and compile manually each object, and it gives the same errors ..

""Warning: Procedure altered with compilation errors.""

Please suggest me with your thoughts.. how to fix this issue..

Thanks in advance..

You post the equivalent of my car does not go.
Tell me how to make my car go.

You need to actually fix the errors that you decided to not share with us.

Try to compile one of the failing procedures then do as below
SHOW ERROR

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Procedure altered with compilation errors [message #636027 is a reply to message #636015]

Wed, 15 April 2015 03:01

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

Or query user_errors.

It's likely some objects you need haven't been imported. Check the import log file.

Report message to a moderator

Re: Procedure altered with compilation errors [message #636044 is a reply to message #636015]

Wed, 15 April 2015 09:53

krish96
Messages: 74
Registered: October 2012

Member

I did try this manually ..

SQL> alter procedure OWNER.OBJECTNAME compile;

Warning: Procedure altered with compilation errors.

SQL> show errors
Errors for PROCEDURE OWNER.OBJECTNAME:

LINE/COL ERROR
-------- -------------------------------------------------
13/13 PL/SQL: SQL Statement ignored
13/89 PL/SQL: ORA-00942: table or view does not exist

accordin to error..When i check these tables are valid and available..

Report message to a moderator

Re: Procedure altered with compilation errors [message #636045 is a reply to message #636044]

Wed, 15 April 2015 09:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>accordin to error..When i check these tables are valid and available..

One of two realities exist.
1) You are correct & Oracle wrongly reports error when it should not do so.
2) Oracle is correct & you are mistaken.

If #1 is the case then you need to submit a Bug Report to Oracle support.

I am willing to bet you that #2 is what exists.
A frequent cause for ORA-00942 is a permissions/privilege problem the logged in USER does not have the required privilege to access the desired object.
This can occur when PRIVILEGE or SYNONYM does not exist.

If you insist on obfuscating object names & user names we will have a difficult or impossible time in trying to solve this mystery for you.

Report message to a moderator

Re: Procedure altered with compilation errors [message #636049 is a reply to message #636044]

Wed, 15 April 2015 10:17

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

ORA-00942: table or view does not exist

As BlackSwan said, the most likely cause of this is that the OWNER of the procedure hasn't been granted privilege (SELECT) on the table in question; it is in a different schema.

Report message to a moderator

Re: Procedure altered with compilation errors [message #636059 is a reply to message #636049]

Wed, 15 April 2015 13:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Just curious, why did you upgrade and then import using datapump. The upgrade will work without reimporting objects.

Report message to a moderator

Previous Topic:	SQL/PLSQL
Next Topic:	Need to know about some basic for explain plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 08:54:53 CDT 2024