Help: How can I find the error in a Procedure programtically

[charlie cs]

We have a procdure. Every night it will become invalid, but it will be
recompiled automatically by our scripts. There is no bad influence on our
production, so this problem was unattended for a long time.

I am interested in writing a scripts, which will record why the procedure is
invalid.

Could some one tell me which dynamic view I should look to get the error
message if a procedure becomes invalid?

Your help is highly appreciated.

[Anurag Varma]

"charlie cs" <cs3526(no spam)@yahoo.com> wrote in message
news:A3yMa.9186$tw3.7756@nwrdny02.gnilink.net...

> We have a procdure. Every night it will become invalid, but it will be
> recompiled automatically by our scripts. There is no bad influence on our
> production, so this problem was unattended for a long time.
>
> I am interested in writing a scripts, which will record why the procedure

is

> invalid.
>
> Could some one tell me which dynamic view I should look to get the error
> message if a procedure becomes invalid?
>
> Your help is highly appreciated.
>
>

Try looking in user_errors / all_errors / dba_errors to see if it provides a
clue.
.... and next time, please post the Oracle version.

Anurag

[Brian Peasland]

It may not have been any error that caused your stored proc to go
INVALID. Rather, it may be DDL statements on an underlying object which
makes the stored proc go INVALID. The next time the stored proc gets
accessed, it is recompiled and everything goes fine. Check the
LAST_DDL_TIME of DBA_OBJECTS for those objects that the stored proc
uses.

HTH,
Brian

charlie cs wrote:

>
> We have a procdure. Every night it will become invalid, but it will be
> recompiled automatically by our scripts. There is no bad influence on our
> production, so this problem was unattended for a long time.
>
> I am interested in writing a scripts, which will record why the procedure is
> invalid.
>
> Could some one tell me which dynamic view I should look to get the error
> message if a procedure becomes invalid?
>
> Your help is highly appreciated.

--
================================================== =================

Brian Peasland
[email]oracle_dba@remove_spam.peasland.com[/email]

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[Daniel Roy]

I looked at bit around to find what you're looking for, and it seems
to me that this info isn't stored by Oracle. At least not in the
dynamic views (the V$ and its underlying tables such as sys.error$,
sys.obj$, ...). The only place left to check for this info is the
fixed tables (get the list from v$fixed_tables to see the list if
you're interested). You can see the procedure's dependencies in
USER(DBA)_DEPENDENCIES, which might be useful to you. This is what
Oracle uses internally (or I sould say sys.dependency$) to decide
which objects to invalidate when an underlying object is altered. Hope
that helps a bit.

Daniel

> We have a procdure. Every night it will become invalid, but it will be
> recompiled automatically by our scripts. There is no bad influence on our
> production, so this problem was unattended for a long time.
>
> I am interested in writing a scripts, which will record why the procedure is
> invalid.
>
> Could some one tell me which dynamic view I should look to get the error
> message if a procedure becomes invalid?
>
> Your help is highly appreciated.

[Stephen_CA]

"charlie cs" <cs3526(no spam)@yahoo.com> wrote in message news:<A3yMa.9186$tw3.7756@nwrdny02.gnilink.net>...

> We have a procdure. Every night it will become invalid, but it will be
> recompiled automatically by our scripts. There is no bad influence on our
> production, so this problem was unattended for a long time.
>
> I am interested in writing a scripts, which will record why the procedure is
> invalid.
>
> Could some one tell me which dynamic view I should look to get the error
> message if a procedure becomes invalid?
>
> Your help is highly appreciated.

Hi,
When a procedure becomes invalid it doesn't produce an error message.
Often it's a case of a change to an object on which the procedure
depends. You say that it becomes invalid every night..perhaps
something on which the procedure depends is being changed in a nightly
script? You may be able to track this down using these views:
DBA_DEPENDENCIES
USER_DEPENDENCIES
ALL_DEPENDENCIES

I hope this is of some help,

Steve