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

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
> >
> > -g

Please 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 Thu Jan 11 2007 - 13:14:45 CST