Home » SQL & PL/SQL » SQL & PL/SQL » Stale Updates and Explicit Cursors
icon5.gif  Stale Updates and Explicit Cursors [message #254457] Thu, 26 July 2007 16:06 Go to next message
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
Junior Member
"Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria"
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/06_ora.htm#sthref736

Ok, so after the explicit cursor gets the resulting set, lets say one of the values changes in one of the rows that are in this resulting set (from some other process). Will the new value be returned after a fetch is performed on the row or will the old value be returned?

I'm not sure how to test this question as it requires simultaneous transactions occurring at the same time.
Re: Stale Updates and Explicit Cursors [message #254460 is a reply to message #254457] Thu, 26 July 2007 16:47 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
When all else fails RTFM
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref130

>I'm not sure how to test this question as it requires simultaneous transactions occurring at the same time.
How about by having TWO windows/sessions into the DB at the same time?

Oracle ALWAYS presents a read consistant view
Re: Stale Updates and Explicit Cursors [message #254522 is a reply to message #254457] Fri, 27 July 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Curious, same kind of question than yesterday there: http://www.orafaq.com/forum/t/86256/102589/.

Quote:
so after the explicit cursor gets the resulting set...

Oracle does not "get" the result set, it identifies it.

Regards
Michel
Re: Stale Updates and Explicit Cursors [message #254697 is a reply to message #254457] Fri, 27 July 2007 10:07 Go to previous messageGo to next message
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
Junior Member
Re anacedent:
- YOU like to SHOUT don't YOU? Doesn't impress me, and is certainly not professional. Suggest reading:
http://en.wikipedia.org/wiki/Netiquette#Forum_etiquette
- If everyone knew every line of the Oracle documentation (even for different releases), then what the hell is the point of this forum? Not all of us are Oralce DBAs or even becoming one. Providing a URL link to the documentation location is good enough, not a silly comment like RTFM.
- Having two windows/sessions open at same time solves nothing, as there will be an unacceptable latency period for manual submissions.

Re Michel Cadot:
- If this sort of question comes up frequently, maybe there should be a FAQ for it? Not all of us are on here day after day (I actually haven't been here for a long time) and so don't know if it has been recently responded to in some other similar fashion.
Re: Stale Updates and Explicit Cursors [message #254710 is a reply to message #254697] Fri, 27 July 2007 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're a little bit paranoiac.
Quote:
YOU like to SHOUT don't YOU? Doesn't impress me, and is certainly not professional. Suggest reading:

I don't think Ana wanted to shout just to emphasize the key words. It is not in Ana's habit to shout.
Quote:
If this sort of question comes up frequently, maybe there should be a FAQ for it?

I didn't mean it's happened frequently and this was not an attack against you, just this question does not raise in the previous six months and then it appears twice in 24 hours, so it's surprised me. Just that, nothing more.

In the sticky you surely read it is said:
Quote:
And as a rule of thumb: be polite. No one is paid for answering your question. The least you can do is show some respect to those who are willing to dedicate some of their spare time to look at your problem.


Regards
Michel
Re: Stale Updates and Explicit Cursors [message #254788 is a reply to message #254697] Sat, 28 July 2007 05:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@alp0001: If you're so touchy, you would better stay away from forums!

alp0001 wrote on Fri, 27 July 2007 17:07
- Having two windows/sessions open at same time solves nothing, as there will be an unacceptable latency period for manual submissions.


You could add a dbms_lock.sleep(100000); in between fetches.
That should give you ample time to change values.
Re: Stale Updates and Explicit Cursors [message #254834 is a reply to message #254697] Sun, 29 July 2007 01:23 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
You don't need to change the value from a different session. You can do change the value from the same session. And unless yo can think of some good reason why changing the value from the same session is any different than changing the value from a different session, then this test should prove the statement.
SQL> SET SERVEROUTPUT ON
SQL> DELETE t;

1 row deleted

SQL> INSERT INTO t VALUES(1);

1 row inserted

SQL> SELECT * FROM t;

                                      A
---------------------------------------
                                      1

SQL> DECLARE
  2      CURSOR c IS SELECT * FROM t ORDER BY a;
  3      i INTEGER;
  4  BEGIN
  5  
  6      OPEN c;
  7      UPDATE t SET a = 2;
  8      FETCH c INTO i;
  9      CLOSE c;
 10      dbms_output.put_line(i);
 11  
 12      OPEN c;
 13      FETCH c INTO i;
 14      CLOSE c;
 15      dbms_output.put_line(i);
 16      COMMIT;
 17  END;
 18  /

1
2

PL/SQL procedure successfully completed

SQL> SELECT * FROM t;

                                      A
---------------------------------------
                                      2

SQL> 
Re: Stale Updates and Explicit Cursors [message #254835 is a reply to message #254834] Sun, 29 July 2007 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And this shows what?
That updating a value you see it updated?

Regards
Michel
Re: Stale Updates and Explicit Cursors [message #254878 is a reply to message #254835] Sun, 29 July 2007 23:28 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
My god, I don't know what I was thinking. I thought that by showing an example in which a row had a value of 1 when a cursor was opened but changed to 2 before the row was fetched yet still fetched a value of 1 had some relevance to the OP's question. Hopefully, if I keep reading this forum long enough, I will learn when it is appropriate to tell a newbie to RTFM when the question is actually a request for a clarification of a statement in the manual. Maybe I'll even figure out how the simple response of RTFM isn't, in itself, impolite and confrontative. It seems to me the F actually stands for something. But what do I know.
Re: Stale Updates and Explicit Cursors [message #254903 is a reply to message #254878] Mon, 30 July 2007 01:01 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
[EDIT: Sorry Scott, I misread your post; I overlooked the different fetches you did! Removed non-relevant content, showing what you already showed.]

[Updated on: Mon, 30 July 2007 01:07]

Report message to a moderator

Previous Topic: equivalent to rank in 7.3
Next Topic: group by to_number()
Goto Forum:
  


Current Time: Tue Dec 06 08:12:28 CST 2016

Total time taken to generate the page: 0.11579 seconds