Re: What controls automatic recompile of stored procedure

In article <PW7Rc.2189$H85.1537_at_newssvr27.news.prodigy.com>, Mark Bole <makbo_at_pacbell.net> wrote:

>> "Jeff" <jeff_at_work.com> wrote in message

>>>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.

>However that does not seem to be your case. From what I glean from the
>9i App Developer's guide, your situation is more like the following:
>"Some Oracle tools, such as Oracle Forms, contain a PL/SQL engine that
>lets you run PL/SQL locally". This could explain why the ODBC and

This is pretty much what I was thinking/saying. Oracle Forms 6i, for example, uses an Oracle 8.0 PL/SQL engine and will not compile/allow 9i PL/SQL. You can see the need for Forms to have its own PL/SQL engine because it allows the storing and running of named program units (not just anonymous blocks) that are NOT stored in the database.

The error message he's getting is probably from recompiling whatever local (to the client) program unit that became invalid when the (remote) SP became invalid because the (remote) table changed. Changing REMOTE_DEPENDENCIES_MODE may keep the local program unit from invalidating, but it won't keep the remote SP from invaliding nor force automatic recompiling.

Therefore, an automated job needs to be setup on the database to recompile invalidated SP's regularly if they are likely to be invalidated regularly. Received on Mon Aug 09 2004 - 16:04:31 CEST