Re: Recompiling invalid objects in PL/SQL

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sat, 10 Dec 2005 11:35:45 +0100
Message-ID: <dneaek$l9i$1@news1.zwoll1.ov.home.nl>

Jeremy wrote:

> In article <1134171805.528351_at_jetspin.drizzle.com>, DA Morgan says...
> 

>>Jeremy wrote:
>>
>>>In article <dn6qlh$qef$1_at_news6.zwoll1.ov.home.nl>, Frank van Bortel
>>>says...
>>>
>>>
>>>>Apart from all the good examples, do you realize Oracle will
>>>>recompile on an 'as needed' base?
>>>>So, unless you're not sure weather everything actually will
>>>>compile, there is no reason to.
>>>>
>>>
>>>
>>>Not necessarily - I beileve that the user trying to execute a package
>>>that is invalid will need to have some specific privilege such as
>>>
>>>SQL> grant alter any procedure to <user>;
>>>
>>>Unless this has changed in 10g?
>>>
>>
>>Depends on who owns it.
>>

> 
> 
> In this scenario USER1 owns all the packages. USER2 has access to 
> execute the packages via a role. The packages have public synonyms.
> 
> Unless USER2 is given the privilege to alter procedures, is there a way 
> that the automatic recompilation can be triggered?
> 

USER1 will recompile the procedure, trigger, function (what ever PL/SQL) the moment it is used, and invalid.
A possible error may occur when "revalidation" (as Oracle calls it) fails.

So, there's no magic, Oracle does, and always has been doing, it when needed.

Why not try it? Create a table, and a procedure that manipulates that table. Grant execute to user2, test. Drop and recreate the table (this will invalidate the proc). Test again.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Sat Dec 10 2005 - 04:35:45 CST