ref cursors and memory usage

From: Jeff Smith <jsmit234_at_ford.com>
Date: Fri, 14 Feb 2003 10:21:19 -0500
Message-ID: <b2j1hf$6bl13@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; Received on Fri Feb 14 2003 - 09:21:19 CST