Re: What controls automatic recompile of stored procedure
Date: Fri, 06 Aug 2004 02:46:33 GMT
Message-ID: <diCQc.876$6_2.207_at_newssvr29.news.prodigy.com>
Hill, James [MNBL1:9478:EXCH] wrote:
> "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: >> [Quoted] >>>Is there something that controls the automatic recompiling of stored >>>procedures? I have a stored procedure that is dependent on some tables
>
[...]
>>
>
> The database is Oracle 8.1.7 on Solaris 2.8, the UNIX client is a fragile
> 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.
>
>
Short answer, the implicit run-time compilation is automatic and can't be turned off, so don't look for some magic parameter (the original question you asked).
On the Unix client (is your error coming from the SCO box or the Solaris box?), log in as a Unix user, set your ORACLE_HOME and PATH (following the example on your Windows box, if necessary), then when you type "sqlplus" at the command line the login banner should at least show you the version. At this point, you can re-try with Unix SQL*Plus the same test you've been trying from Windows SQL*Plus. This should help confirm whether or not the broker software is using the same Oracle client libraries as what you have installed on the server.
On the database side, assuming some kind of scheduled job triggers the replication, follow it with something like the following, which will compile the procedures with minimal involvement on the part of the DBADMIN.
exec DBMS_UTILITY.COMPILE_SCHEMA ('schema_name');
--Mark Bole Received on Fri Aug 06 2004 - 04:46:33 CEST