Cursor concept

From: Gerard Lacroix <kochel_verz_at_yahoo.com>
Date: 7 Dec 2005 18:26:06 -0800
Message-ID: <1134005308.764144.78180@g14g2000cwa.googlegroups.com>

Hi !
I tried to show a friend that pl/sql cursors are in fact pointers and do not retrive all rows when you open them. So I made this very simple test case, but I don't understand the output. I think the answer comes from the multiversioning side, but I 'd like to understand clearly this case with a cursor. May be it is not just a memory position isn't it ?

Thanks.

C:\>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 7 22:40:55 2005

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create table t as select rownum a from user_objects where rownum < 10;

Table created.

SQL> declare
1 cursor cur_test is

  2      select a
  3      from t
  4      order by a;

  5 v_test t.a%type;
  6 cont boolean := TRUE;
  7 begin
  8 open cur_test;
  9 loop

 10       fetch cur_test into v_test;
 11       exit when cur_test%notfound;
 12       if cont then
 13          begin
 14            delete from t where a = 9;
 15            cont := FALSE;
 16            commit;
 17          end;
 18       end if;
 19       dbms_output.put_line(v_test);

20 end loop;
21 close cur_test;
22 end;
23 /
1
2
3
4
5
6
7
8
9

PL/SQL procedure successfully completed.

SQL> Received on Wed Dec 07 2005 - 20:26:06 CST