Re: No FOR loops for cursor variables?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 19 May 1998 20:11:59 GMT
Message-ID: <3564e5b7.25106431@192.86.155.100>

A copy of this was sent to "Curtis Duhn" <no.spam.please_at_usa.xerox.com> (if that email address didn't require changing) On Mon, 18 May 1998 20:20:07 GMT, you wrote:

>I want to do this...
>
>DECLARE
> TYPE MY_CURSOR_TYPE IS REF CURSOR RETURN my_table%ROWTYPE;
> my_cursor MY_CURSOR_TYPE;
>BEGIN
> OPEN my_cursor FOR SELECT * FROM my_table;
> FOR my_cursor_rec IN my_cursor
> LOOP
> dbms_output.put_line(my_cursor_rec.name);
> END LOOP;
>END;
>
>It doesn't work. I've about reached the conclusion that cursor FOR loops
>don't work for cursor variables. Is this true? I can't think of any
>logical reason why FOR loops couldn't be implemented for REF CURSORs. Can
>someone explain this to me please?
>

because a 'static' cursor is executed like:

SQL> declare
  2 cursor c1 is select * from emp;   3 begin
  4 for x in c1 loop
  5 dbms_output.put_line( x.ename );   6 end loop;
  7 end;
  8 /

It implicity declares a record X (which is can do, the shape of C1 is well known at compile time), Opens the cursor for you (which is can do, C1 is well known at compile time), fetches each row into X and closes C1 when it finds the last row.

As compared to

SQL> l
  1 DECLARE

  2      TYPE MY_CURSOR_TYPE IS REF CURSOR;
  3      my_cursor    MY_CURSOR_TYPE;
  4     emp_rec         emp%rowtype;
  5     dept_rec        dept%rowtype;
  6  BEGIN
  7     if ( 1 = 2 ) then
  8             OPEN my_cursor FOR SELECT * FROM dept;
  9     else
 10             open my_cursor for select * from emp;
 11     end if;
 12     loop
 13             if ( 1 = 2 ) then
 14                     fetch my_cursor into dept_rec;
 15                     exit when my_cursor%notfound;
 16             else
 17                     fetch my_cursor into emp_rec;
 18                     dbms_output.put_line( emp_rec.ename );
 19                     exit when my_cursor%notfound;
 20             end if;
 21      END LOOP;
 22     close my_cursor;

where pl/sql cannot implicity declare a record for a cursor variable since the shape of a cursor variable does not need to be known at compile time, likewise, it cannot open the cursor since the query is not know until run time.

In short, since you open a cursor variable explicitly and you must open it explicity, the implicit 'cursor for loop' cannot work with it.

>To reply, replace "no.spam.please" with "curtis.duhn" in the following email
>address:
>no.spam.please_at_usa.xerox.com
>
>Thanks,
>Curtis Duhn
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue May 19 1998 - 15:11:59 CDT