Subject: Re: Invalid status after successful run of procedure
Newsgroups: comp.databases.oracle.server
References: <33E7A5EB.5684@usa.net>
Organization: InterAccess, Co. - Chicagoland's Full Service Internet Provider
Reply-To: akaplan@interaccess.com
Avinash Kumar (avinash_kumar@usa.net) wrote:
: Hello everybody,
:
: I have procedure, which gets invalid after a successful run
: i.e. desc procedure_name -- shows status invalid.
:
: The procedure first drops an existing view and then immediately
: creates the view using dbms_sql and later uses the newly created
: view.
: The procedure runs fine, except that it gets invalid after successful
: run. Every object that it uses is still valid. Surprisingly,
: doing an " alter procedure p_name compile " sets it fine again.
: Any suggestions why ?
:
: Thanks in advance,
:
: Avinash Kumar
Avinash,
What is happening is that while the procedure runs, it is dropping an
existing view. At that point, Oracle marks the procedure as invalid, since
it references the view that was dropped. Creating the view again does not
re-validate the procedure.
You should look at the DBA_DEPENDENCIES view for a list of all objects
that the procedure "depend" on. If any of these objects are dropped or
become invalid, then your stored procedure will also become invalid.
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 100+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page