Re: ref cursors and memory usage

On Fri, 14 Feb 2003 23:24:44 GMT, "Ryan" <rgaffuri_at_cox.net> 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?
>

I found this:

Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. (Recall that consecutive OPENs of a static cursor raise the predefined exception CURSOR_ALREADY_OPEN.) When you reopen a cursor variable for a different query, the previous query is lost.

here:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm#1614

John Received on Sat Feb 15 2003 - 16:02:03 CST