Re: Cursors and SCN : consistent read ??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 22 Apr 1998 11:38:02 GMT
Message-ID: <353ed50d.2319795@192.86.155.100>

A copy of this was sent to philippe <philippe_at_arpege.imaginet.fr> (if that email address didn't require changing) On Wed, 22 Apr 1998 09:10:21 +0200, you wrote:

>When I open a cursor, I do see records that where appended by other
>people after I opened I. T thought that Oracle used SCN to provide a
>consistent view of data, i.e a view of data having the correct SCN. Can
>somebody explain this to me ??
>
>Best Regards,
>
> Philippe (philippe_at_arpege.imaginet.fr)
> --- One day I'll be a DBA !!! ---

Can you send an example. What you say above, isn't the way it works. Once you open the cursor, its result set is 'pre-ordained'. We haven't gotten it yet, but it is a fixed result set. I ran the following test (package spec/body of cv_demo is below the example). The cursor is searching for all EMPS whose ename contains a capital A in it:

Session1                                Session2
------------------                      -------------------

QL> variable RefCur refcursor
SQL>
SQL> begin

  2          cv_demo.open_cursor
             ( '%A%', :refCur );

  3 end;
  4 /
PL/SQL procedure successfully completed.

                                       SQL> update emp set ename = lower(ename);
                                       14 rows updated.

                                       SQL> insert into emp values ( 1, 'AAAA',
                                            'x', null, null, null, null, null );
                                       1 row created.

                                       SQL> commit;
                                       Commit complete.

SQL> print refCur

ENAME MGR
---------- ----------

ALLEN            7698
WARD             7698
MARTIN           7698
BLAKE            7839
CLARK            7839
ADAMS            7788
JAMES            7698

7 rows selected.

SQL> begin
  2 cv_demo.open_cursor( '%a%', :refCur );   3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> print refCur

ENAME MGR
---------- ----------
AAAA

---

So, session1 had to reopen the cursor to see the changes made and committed by session2...

Can you post an example that shows otherwise?

create or replace package cv_demo
as

    type my_rec is record
    (

        ename   emp.ename%type,
        mgr     emp.mgr%type

    );
    type emp_cur is ref cursor return my_rec;

    procedure open_cursor( p_ename_like in     varchar2,
                           p_cur_rec    in out emp_cur );

end;
/

create or replace package body cv_demo
as
procedure open_cursor( p_ename_like in varchar2,

                       p_cur_rec    in out emp_cur )

is
begin

    open p_cur_rec for select ename, mgr

                         from emp
                        where ename like upper(p_ename_like);

end;
end;
/  

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 Wed Apr 22 1998 - 06:38:02 CDT