Re: Is it possible to know the reason for invalid function

Dear,

Well, I am familiar with the concept. But these two schemas (living in the same database) do not talk to each other in any way. there are no public synonyms in the system (by policy) and there are no cross references. As I suggested, these two are two diff environments for same applications (dev and test), they just don't talk to each other.

I hope I have added to the clarity of the situation.

regards
raghav..

On Jan 11, 8:14 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Raghav wrote:
> > Hi guys,
>
> > thanks for your replies... but, ..... Lets me elaborate on my
> > situation....
>
> > I have three databases...
>
> > A, B, C
>
> > A has two schema a1 and a2.
>
> > a1 is my test environment and has synonyms to B (using db links)
>
> > a2 is my dev environment and has synonyms to C (using db links)
>
> > the problem that i am facing is...
>
> > when i compile function f1 in a1 it compiles fine, but the function in
> > f2 becomes invalid. The same is true vice versa. i.e. I compile f1 in
> > a2 and its fine, but f1 in a1 becomes invalid.
>
> > since there are no errors at the time of compilation, your suggestions
> > cannot really be THE solution to situation. What I look for is, for
> > what reason the other schema's f1 gets invalidated. I believe the
> > oracle metadata stores that kind of info.
>
> > Looking forward to your responses...
>
> > regards
> > raghav..
>
> > On Jan 11, 11:27 am, "gazzag" <gar..._at_jamms.org> wrote:
> > > Raghav wrote:
> > > > Hi,
>
> > > > Is it possible to figure out the reason due to which a function is
> > > > invalid at any point in time ?
>
> > > > Kindly help.
>
> > > > regards
> > > > raghav..SQL> ALTER FUNCTION <function_name> COMPILE;
>
> > > Warning: Function altered with compilation errors.
>
> > > SQL> SHOW ERRORS
>
> > > or
>
> > > SQL> SELECT * FROM USER_ERRORS;
>
> > > HTH
>
> > > -gPlease do NOT top post.
>
> You apparently aren't familiar with the concept that any dependent
> objects for a procedure can invalidate a procedure if those objects are
> changed. And, apparently, these procedures depend upon each other so
> that when you compile one it invalidates the other since the dependend
> object (the recently coimpiled procedure) has changed (it's been
> recompiled).
>
> This intertwined dependency you have created will always result in this
> behaviour. You need to change these procedures so they do not depend
> upon each other, otherwise you'll keep this endless loop of
> valid/invalid procedures going.
>
> David Fitzjarrell
Received on Fri Jan 12 2007 - 04:21:31 CST