Hi
How does this work?
Session 1: Session 2:
SQL> create table t(x number);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> begin
2 for f in (select * from t) loop
3 dbms_output.put_line(sysdate||' '||f.x);
4 dbms_lock.sleep(60);
5 end loop;
6 end;
7 /
SQL> drop table t;
Table dropped.
SQL> select sysdate from dual;
13:58:45
13:58:34 1
13:59:34 2
The table was dropped but the row is still returned AFTER the table is dropped. Using OPEN and FETCH behaves the same
declare
cursor c is select * from t;
r t%rowtype;
begin
open c;
fetch c into r;
dbms_output.put_line(sysdate||' '||r.x);
dbms_lock.sleep(60);
fetch c into r;
dbms_output.put_line(sysdate||' '||r.x);
end;
/
Is this a bug? I could even reproduce it with one million row. Should not this return ORA-01001 invalid cursor ?
begin
for f in (select * from t) loop
if f.x in (1,10,100,1000,10000,100000,1000000) then
dbms_output.put_line(sysdate||' '||f.x);
dbms_lock.sleep(5);
end if;
end loop;
end;
/
Thanks
Laurent