| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Cursors and transactions
The effect you are seeing is called "consistent read". In Oracle, the result set returned by a cursor is determined at the time the cursor is opened. You can:
declare
cursor c1 is select deptno from dept;
l_deptno number;
begin
open c1;
delete from dept;
commit;
loop
fetch c1 into l_deptno;
exit when c1%notfound;
dbms_output.put_line( l_deptno );
It will print out whatever was in DEPT before the delete/commit took place (since the cursor was opened before the delete/commit took place).
You will need to close the cursor and reopen it in order to not 'see' the inserted data.
One of the most glaring reasons for this behaviour can be explained with an example. Suppose the opened cursor could see the deleted/updated/inserted data after the cursor was opened. What would the following loop do:
delcare
cursor c1 is select deptno from dept;
dept_rec dept%rowtype;
begin
open c1;
loop
fetch c1 into dept_rec
insert into dept values
( dept_rec.deptno+100, dept_rec.dname, dept_rec.loc );
commit;
Well, if the opened cursor C1 could see the inserted data, then the above would be an infinite loop (i've seen this in other databases).....
There are lots of other cases as well...
On 24 Jan 1997 13:25:08 GMT, "Andrea" <A.Savino_at_agora.stm.it> wrote:
>Sorry for my english,
>Some one can help me about this PL-Sql function.
>
>I would like know if a write this :
>
>....
>CURSOR c_test IS
> SELECT id, errore
> FROM messagges;
>
>.....
>INSERT INTO messages VALUES (100, 'error 1');
>....
>INSERT INTO messages VALUES (101, 'error 2');
>...
><< some others statments>>
>...
>OPEN c_test;
>ROLLBACK;
>->> here I see again my rows ? Is it possibile to a do a loop on that
>cursor o
>the rollback delete also in memory my rows ?
>
>Thanks
>
>
>--
>Andrea
>A.Savino_at_stm.it
>
>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |