Re: Recursion and cursor name - ORA-01002 fetch out of sequence

On Fri, 28 Nov 1997 16:36:02 +0100, Dietmar Leibecke <dleibeck_at_debis.com.---> wrote:

>Hello,
>
>in a function that gets called recursively a cursor will be opened and fetched.
>When entering the function a second time the cursor can be opened and fetched
>without any warning or error message. After returning to the calling function I
>try to fetch the next record, but Oracle claims "ORA-01002 fetch out of
>sequence" - the cursor state is invalid or closed.
>
>What can I do to recursively call a function that needs database access via a
>cursor? Is there a way to use a dynamic cursor name?
>
>Any input welcome,
>
>--
>Dietmar Leibecke
>----------------
>Address is altered to discourage junk mail.
>Remove ".---" for the real address.
>
>

I just tried this with oracle 8 and it worked ok except that it eventually hit the too many open cursors error. procedure fred as

   cursor c is
   select dummy from sys.dual;
   dummy char(1);
begin

   open c;
   fetch c into fred.dummy;
   fred;
   close c;
end;

the following also worked, although I killed the process as it was gradually eating memory.
procedure fred as

   cursor c is
   select dummy from sys.dual;
   dummy char(1);
begin

   open c;
   fetch c into fred.dummy;
   close c;
   fred;
end; Received on Fri Nov 28 1997 - 00:00:00 CST