Re: Multiple Cursors in PL/SQL?

From: Vijay Darekar <vijayd_at_worldnet.att.net>
Date: 1997/06/15
Message-ID: <33A4E3C9.D9@worldnet.att.net>#1/1

Michael Fanelli wrote:
>
> This is really strange! I have a small piece of PL/SQL code that has two
> explicit cursors. There are two loops: the outer loop fetches records from
> cursor 1, the inner loop fetches records from cursor two based on the
> results from cursor 1. The inner loop always does a CLOSE before exiting
> back to the outer loop.
>
> The problem is that I keep getting a "cursor already open" error on the
> inner loop. I have tried putting a "IF C2%ISOPEN ...." to close the cursor
> right before I open it again. Yet, nothing helps.
>
> The only reason I can come up with (and it does seem unlikely) is that
> PL/SQL can only have one cursor open at a time. Is this true? Thanks!
>
> mike
> ------------------------------------------------------------------------
> mfanelli_at_dimensional.com

You can have more than one cursor open in the PL/SQL the no. of open cursor limit is decided in init_ora by setting open_cursor parameter.

In your PL/SQL you can use the FOR loop so that you don't have to open and close the cursor exclusively.

cursor c1 is

   select *
   from table_name_1;

Cursor c2 is

   select *
   from table_name_2
   where col_2 = l_col;

Begin
  for c1_rec in c1
  loop

     l_col := c1_rec.col_1;
     for  c2_re in c2
     loop
       -------
     end loop;

  end loop;
end;

Try this .

Vijay Darekar Received on Sun Jun 15 1997 - 00:00:00 CDT