Re: What controls automatic recompile of stored procedure
Date: Sat, 07 Aug 2004 17:02:39 GMT
Message-ID: <PW7Rc.2189$H85.1537_at_newssvr27.news.prodigy.com>
Hill, James [MNBL1:9478:EXCH] wrote:
> 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.
>
> "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? >> [Quoted] >>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.
>
>
The following is not based on extensive experience with this exact topic (which I do not have), but rather "thinking aloud" to try to help. Corrections/additions from the group welcome.
Normally the whole remote dependencies thing implies a local and a remote database, and use of a DBLINK. (This I am more familiar with). [Quoted] 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 SQL*Plus work fine (no "remote", you are running PL/SQL directly on the server) but your library-based client fails (locally-compiled PL/SQL anonymous block connecting to "remote" server database).
Putting this together with your clue about the REMOTE_DEPENDENCIES_MODE, it seems that setting this in init.ora won't help (your client PL/SQL doesn't have an init.ora). So the next thing I would try is setting it for your session:
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE Your Openlink broker would need to execute this after logging in to the database, hopefully you have control over that step of the process.
Again, from the App Developers Guide:
"Client-side PL/SQL users should set the parameter to SIGNATURE. This allows:
- Installation of new applications at client sites, without the need to recompile procedures.
- Ability to upgrade the server, without encountering timestamp mismatches."
None of this follows from the original ORA-0403 error you got, but it wouldn't be the first time a misleading error message failed to help pinpoint the problem. An error such as "ORA-04045 errors during recompilation/revalidation" would have been more conclusive.
On the other hand, the whole thing goes back to, what does it take to trigger implicit runtime re-compilation of your "remote" procedure? Simply matching the signature of the remote procedure with the signature you have locally won't help if the procedure itself is still marked invalid because a table it depends on was changed. (Can you even try to compare signatures if the object is invalid?) So this may be a false trail, but it wouldn't hurt to try the "alter session".
(There is also a brief reference to differences between PL/SQL v.1 clients and v.2 clients -- your legacy system may be the former.)
So, don't forget the other option of just running the "compile schema" procedure on a scheduled basis, to automate what you currently do by hand to work around the problem. This may be the only way to get the remote procedure recompiled when you need it.
--Mark Bole Received on Sat Aug 07 2004 - 19:02:39 CEST