Re: What controls automatic recompile of stored procedure

[Quoted] "Mark Bole" <makbo_at_pacbell.net> wrote in message news:moAQc.833$JX1.35_at_newssvr29.news.prodigy.com...
> Hill, James [MNBL1:9478:EXCH] 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 has "implicit run-time recompilation", as your SQL*Plus and ODBC
> tests confirm. What version and platform are your database and client
> running on? Perhaps the "OpenLink UDBC library" is an unsupported
> client version for the database your are running. You could always
> explicitly compile everything after your replication, too.
>
> --Mark Bole
>
The database is Oracle 8.1.7 on Solaris 2.8, the UNIX client is a fragile [Quoted] old legacy system running SCO 5.0 using the OpenLink UDBC library to talk to a broker that runs on the Solaris box.

Yes, I suspect that there is something incompatible or unsupported with the OpenLink library. The problem is, I'm not sure what to look for or ask as to what might be unsupported. The OpenLink broker connects to Oracle through it's local listener (on the same server as Oracle), but my SQLPlus and ODBC are using the OCI client installed on my PC. So I was looking for possibly something in the client configuration that might enable or disable this normal operation.

The issue is that the replication changes typically happen during the night, and my DBADMIN is not "responsible" for the stored procedures in my schema of the database. Consequently, my UNIX processes simply get errors until I go back in and recompile them manually. Received on Fri Aug 06 2004 - 03:30:05 CEST