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

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..
run this command :

alter function < function_name > compile;
show error

it will show you the error.

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..
>
yes. check out user_dependencies. If any of the dependent objects
changes then the function will become invalid and needs to be recompiled.

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

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...@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

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...@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

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...@cox.net" <fitzjarr...@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...@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

On Jan 12, 11:21 am, "Raghav" <sharma.raghven...@gmail.com> wrote:
> 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..



Apparently your assertion is incorrect. They *are* intertwined,
otherwise you wouldn't have run into problems.
Could you also please explain why you ignored David's request to stop
top-posting?
Aren't you aware this forum loathes top-posting?
If so why do you insist on top-posting?

--
Sybrand Bakker
Senior Oracle DBA

Raghav wrote:
> 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..
>

Raghav,

You have been asked several times to stop top-posting. I suspect that
you are not being rude; I suspect that you simply don't know what
"top-posting" means.

Top-posting means putting your reply *above* the text that you are
replying to. This makes readability of the thread very difficult for
the majority of this group. Please place your reply _under_ any text
you are replying to or quoting. Like I have done here.

HTH

-g

On Jan 12, 12:20 pm, "gazzag" <gar...@jamms.org> wrote:
> Raghav wrote:
> > 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..Raghav,
>
> You have been asked several times to stop top-posting. I suspect that
> you are not being rude; I suspect that you simply don't know what
> "top-posting" means.
>
> Top-posting means putting your reply *above* the text that you are
> replying to. This makes readability of the thread very difficult for
> the majority of this group. Please place your reply _under_ any text
> you are replying to or quoting. Like I have done here.
>
> HTH
>
> -g

Group,

Forgive me about the top posting. Like "gazzag" mentioend, I didn't
know the meaning of "top-posting". I apologize.

The habit comes from emails.. like in Outlook. I hope I don't repeat
it in future.

regard
raghav..