Re: Question re: ProC and Cursor Cache

Ranga Gopalan wrote:
>
> Hi,
>
> According to the ProC manual, a ProC program which has HOLD_CURSOR=YES and
> RELEASE_CURSOR=NO will not perform a parse for any repeated executions of a
> SQL statement (e.g simple sql statement like exec sql select f1 into
> :f1_val from t1 where f2 = :v1). The parsed representation of the statement
> remains in the Shared SQL cache until it is aged out. If the statement is
> aged out from the - what happens - does the ProC program get an error or
> does the ProC program automagically reparse the SQL or ?? The ProC manual
> (programmers guide to the Oracle ProC precompiler page D-11) says that
> "Normally this is not a problem, but you might get unexpected results if
> the definition of a referenced object changes before the SQL statement is
> reparsed". Can anyone please explain this to me ?
>
> Thanks in advance for your inputs,
>
> Ranga.

<snip>

I believe what will happen is that if the parsed cursor is aged out and overwritten, it will be reparsed automatically the next time it is used and the program will not see any difference except for the nominal time needed to reparse. If the statement is still parsed and the underlying table or index is changed, the execution plan is still in cache and will be used. This may or may not be a valid example, but say you dropped an index. The parsed execution plan still references the index, so you may get an error or unpredictable results when the statement is processed. Reparsing will correct the problem. But in this case I don't know if it would let you drop the index with parsed cursors referencing it, so it's probably just a CYA saying that you may have a problem. I think in practice it will either not allow the changes (by DDL locks, i.e.) or invalidate the cached cursor and force it to reparse the next time it is used.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.