Re: Invalid Procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 28 Jan 2000 12:47:02 -0500
Message-ID: <h7l39sc1v9uh5imvvf1sgngi3hjkvr8lf7@4ax.com>

A copy of this was sent to "Chad Sheley" <csheley_at_usa.capgemini.com> (if that email address didn't require changing) On Fri, 28 Jan 2000 09:33:31 -0600, you wrote:

>Can anyone tell me what mechanism makes packages or procedures invalid when
>a different object that a package or procedure depends upon changes? Is
>there anyway of disabling it?
>

it is just part of the database -- when you compile something, things that depend on it are invalidated. They have to be. You've compiled one part of a large program -- the rest of the stuff must be validated to make sure parameters and constants they refer to still exist after this compilation.

>Or, does anyone know of a "make"-like utility for keeping my packages and
>procedures valid? Or how can I compile a package or procedure .sql script
>from the command line and then use a makefile?

dbms_utility.compile_schema ALTHOUGH there is no need to do this as the dependent objects will automagically compile themselves when executed if they are invalid -- an object does not have to be 'valid' to be executed. It is OK for them to be invalid.

I would just leave them be invalid and let them autocompile next time they are run.

you can do individual ones by issuing:

alter package|procedure|function <name> compile <body -- if its a package body>;

Also, you could consider putting your specs and bodys into different files. If you don't recompile the spec, people who are dependent on packageB will not become invalid -- you shouldn't be recompiling a spec nearly as much as a body. If you cut down on the number of times you compile the spec -- you'll reduce the number of times things go invalid.

>
>BTW, I'm running Oracle 8.05 on an NT box.
>
>Maybe I should describe what's happening...
>
>I have packageA which makes reference to a constant in packageB that is used
>by many other packages. When I recompile packageB, some Oracle process makes
>packageA invalid.
>
>Now, this is an oversimplification of my problem as I have a team of
>developers that have created what seems to be a web of package and procedure
>dependencies. I've suggested reorganizing the packages to reduce these
>development environment problems, but I also told them I'd investigate other
>options.
>
>Any suggestions?
>
>Chad Sheley
>Senior Consultant
>Cap Gemini
>Des Moines, IA
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jan 28 2000 - 11:47:02 CST