Re: What controls automatic recompile of stored procedure

[Quoted] Thanks Jeff, Mark

As it turns out the real problem I'm having stems from an issue with remote dependencies. My stored procedures are dependant on some functions in a remote package. Although it's "interesting" why the UNIX client does not cause a recompile the way my Windows ODBC client does (it's almost like it's looking to see if it's valid before actually executing) the whole issue would go away if I can properly address my remote dependency problem.

I've read about the REMOTE_DEPENDENCIES_MODE but I don't understand where to use it or where it applies. The remote package does indeed change it's TIMESTAMP, but does not change it's SIGNATURE. From what I can tell, I should be able to keep my procedures from invalidating by the proper use of the SIGNATURE instead of TIMESTAMP. I'm not a DBADMIN, just a poor soul trying to muddle through supporting some existing application.

Thanks for your patience.

[Quoted] "Jeff" <jeff_at_work.com> wrote in message news:cevu17$beo$1_at_cronkite.cc.uga.edu...
> In article <ceubkr$8ag$1_at_zcars0v6.ca.nortel.com>, "Hill, James
[MNBL1:9478:EXCH]" <jimhill_at_americasm01.nt.com> wrote:
> >Is there something that controls the automatic recompiling of stored
> >procedures? I have a stored procedure that is dependent on some tables
that
> >are replicated from a remote database. Whenever the table is changed,
the
> >stored procedure become invalid (that makes sense).
> >
> >If I call the procedure using SQLPlus or with a test application using
ODBC
> >from Windows, the procedure recompiles as expected and no errors are
> >presented.
> >
> >But when I call the procedure from a UNIX client using the OpenLink UDBC
> >library, it fails and gives me the error ORA-04043: Object PROC does not
> >exist.
> >
> >Is there something that needs to be set at the session or something else
> >that need to set the default to auto-recompile?
>
> Oracle will not automatically recompile remote dependencies. Could be
that
> your OpenLink UDBC acts as a compiler on the client side and is unable to
> compile the (local) procedure call on the client because of the invalid
> (remote)SP in the Oracle database.
>
> As previously advised, it'd be wiser not to rely on automatic recompiling.
Received on Fri Aug 06 2004 - 23:38:07 CEST