Oracle server process will still hold the cursor. Be careful about the
max number of cursor other than memory allocation in shared pool.
Ryan wrote:
> "Jeff Smith" <jsmit234_at_ford.com> wrote in message
> news:b2j1hf$6bl13_at_eccws12.dearborn.ford.com...
>
>>I am debugging somebody's code and I have question that I cannot find any
>>documentation for.
>>
>>If a package/procedure is reusing the same "ref cursor" within a single
>>procedure, what is going on in memory if the first instance of the cursor
>>
> is
>
>>not closed.
>>
>>For example, in the procedure a ref cursor oCur is opened, fetched and
>>looped through, doing whatever. The cursor is not closed after the
>>
> procedure
>
>>finishes.
>>
>>Then, the same oCur is opened, but this time it is being returned as an
>>
> out
>
>>parameter, and the select statement is entirely different than the earlier
>>one executed.
>>
>>I know that if you assign a ref cursor to another ref cursor, it really is
>>simply pointing to the same result set. (refcur1 = refcur2, they point to
>>the same data).
>>
>>The argument I am having is that I think that each time ref cursor is
>>opened, it should be closed. Can someone explain to me how I can see what
>>
> is
>
>>going on in memory?
>>
>>
>>
>>
>> TYPE IO_Curtype IS REF CURSOR;
>> oCur IO_Curtype;
>> vfoo VARCHAR2(10);
>> vfoo1 VARCHAR2(10);
>>
>>BEGIN
>>
>> OPEN oCur FOR SELECT col1, col2 where ...;
>> LOOP
>> EXIT WHEN oCur%NOTFOUND;
>> FETCH oCur into
>> vfoo1,
>> vfoo2;
>>
>> /* do something */
>> END LOOP; /* but do not close */
>>
>>/* now open oCur again, but this time the select is different */
>> OPEN oCur FOR SELECT COL3, COL4 WHERE ...;
>>
>>end;
>>
>>
>>
>>
>
> good point. Anyone know if you fail to close a ref cursor and reuse it,
> whether this causese a memory leak?
>
>
>
Received on Sat Feb 15 2003 - 07:00:13 CST
