Re: Cursor concept
- From: "William Robertson" <william.robertson@xxxxxxxxxxx>
- Date: 8 Dec 2005 11:18:20 -0800
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.
.
- References:
- Cursor concept
- From: Gerard Lacroix
- Cursor concept
- Prev by Date: Re: PHP oracle fetch out sequence !
- Next by Date: Re: Can you pass a table name into a stored procedure?
- Previous by thread: Re: Cursor concept
- Next by thread: SqlNet problems
- Index(es):
Relevant Pages
|
|
