Re: Cursor concept

Gerard Lacroix wrote:
> 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
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> 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>
That is expected behaviour. Oracle actually went to the rollback segment to retrieve the row you deleted, because the rule is that it should return results as of the instant that the query started, regardless of how long the query has been running. Any other behaviour would lead to inconsistent results if (for example) other sessions made changes to your data during a long-running query.

I think the way that Oracle presents consistent results like this can make it appear as if the changes haven't really been applied, or as though cursors store up all the results in some internal cache before returning any rows (which as you correctly say, they don't), and this can lead to confusion about how it actually works. I've tried to explain the Snapshot Too Old error to a support manager in the past and been flatly disbelieved.

I also think a cursor can be seen in several different ways. Logically it specifies a set (actually "set" implies distinct values, so I should probably say "multiset"), which is why I hate it when people name them things like "get _customers" (it doesn't). Physically it's also an area of memory containing some SQL and associated result data and state information. When it's passed as a variable it's effectively a pointer. Received on Thu Dec 08 2005 - 13:18:20 CST