Re: ref cursors and memory usage

Thanks for that link. Reading it, it seems clear the if you reuse the cursor variable it does not leave the subsequent result sets in memory. However, other examples in the same document indicate when you "fetch" using LOOP that you can explicitly close it:

LOOP
   FETCH emp_cv INTO emp_rec;
   EXIT WHEN emp_cv%NOTFOUND;

• process data record END LOOP; /* Close cursor variable. */ CLOSE emp_cv;

"John Russell" <netnews5_at_johnrussell.mailshell.com> wrote in message news:4vdt4v8h1j0ade13aafv5fu6bc9eiakr4b_at_4ax.com...
> 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.h tm#1614
>
> John
Received on Mon Feb 17 2003 - 06:20:31 CST