| Concurrency problem in cursor [message #321966] |
Wed, 21 May 2008 23:41  |
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
|
Member |
|
|
Dear All,
In the following code,
declare
cursor c1
is
select *
from test_concurrency
where a1 = 'a';
v_counter number(5) := 0;
begin
for c in c1 loop
v_counter := v_counter + 1;
update test_concurrency
set a1 = 'b';
dbms_output.put_line('counter'|| v_counter);
end loop;
end;
what I am expecting is that the loop runs just once -- because all the rows having a1 = 'a' are updated inside the loop.
But, the counter shows that the loop is running exactly the number of times equal to the number of rows having a1='a'.
How can we solve this problem?
Thanks in advance.
Sarwagya.
[Updated on: Wed, 21 May 2008 23:42] Report message to a moderator |
|
|
|
|
|
| Re: Concurrency problem in cursor [message #321973 is a reply to message #321968] |
Thu, 22 May 2008 00:02  |
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
|
Member |
|
|
Thank you anacedent for your quick response.
declare
cursor c1
is
select *
from test_concurrency
where a1 = 'a';
v_counter number(5) := 0;
begin
for c in c1 loop
v_counter := v_counter + 1;
update test_concurrency
set a1 = 'b';
dbms_output.put_line('counter'|| v_counter);
commit;
end loop;
end;
Even when I put a commit at the end of the cursor loop, there was no change in the output.
Output:
counter1
counter2
counter3
counter4
counter5
|
|
|
|
| Re: Concurrency problem in cursor [message #321974 is a reply to message #321966] |
Thu, 22 May 2008 00:05 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> what I am expecting is that the loop runs just once -- because all the rows having a1 = 'a' are updated inside the loop.
When using cursors and loops, you shall read about them in the fine documentation, found eg. online on http://tahiti.oracle.com/.
But, as you want to achieve it in one step, why do you bother with loop and do not issue a single UPDATE statement? Is it a homework on cursors and loops?
By the way, as the UPDATE statement inside the loop has no WHERE condidition, it always updates all rows.
|
|
|
|
| Re: Concurrency problem in cursor [message #321976 is a reply to message #321974] |
Thu, 22 May 2008 00:17 |
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
|
Member |
|
|
The code block I presented is just a simple example.
The actual requirement doesn't do exactly the same.
I mean to say that not all the rows are updated at once.
The update statement is quite different.
I didn't present the actual code because it would be rather confusing.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
