Hi,

         Using Redhat 8.0 + Oracle 9.2.0
         -------------------------------

Ref    :   "Expert one-on-one" 
Topic  : Multi-versioning + Read Consistent Queries

Chapter 1  page no. 34


 "Bear in mind that oracle does not copy the data anywhere when you
  open a cursor"

 I was trying to understand how oracle multi-versioning scheme works
 and gives the consistent result using rollback segment if required.


 

SQL> set serveroutput on
SQL>
SQL> create table a as select * from dict where
  2  rownum < 11;

Table created.

SQL> var x refcursor
SQL>
SQL> begin
  2    open :x for select * from a;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> truncate table a;

Table truncated.

SQL> print x
ERROR:
ORA-08103: object no longer exists



no rows selected


          ### Because we didn't genrate the redo...



SQL>
SQL> drop table a;

Table dropped.

SQL> create table a as select * from dict where
  2  rownum < 11;

Table created.

SQL> begin
  2       for i in ( select * from a ) loop
  3
  4         dbms_output.put_line(' -->> '||i.table_name);
  5         execute immediate ( ' Truncate table a ');
  6      end loop;
  7  end;
  8  /
-->> ALL_ALL_TABLES
-->> ALL_APPLY
-->> ALL_APPLY_CONFLICT_COLUMNS
-->> ALL_APPLY_DML_HANDLERS
-->> ALL_APPLY_ERROR
-->> ALL_APPLY_KEY_COLUMNS
-->> ALL_APPLY_PARAMETERS
-->> ALL_APPLY_PROGRESS
-->> ALL_ARGUMENTS
-->> ALL_ASSOCIATIONS

PL/SQL procedure successfully completed.

SQL> select count(*) from a;

  COUNT(*)
----------
         0

I was expecting the same result but .. ?? 

Sir, Is there any diff.in working logic 
     between Ref_cursor or this Cursor_for_loop ?

Thanks,
Js




 

Dear Tom,

I have a question concerning read-consistency and cursors. It is about the same example p33-34 of your book Expert one-on-one : 

SQL> create table laly as select * from all_objects;

Table created.

SQL> variable rc refcursor;
SQL> begin
  2  open :rc for select * from laly;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> delete laly;

3566 rows deleted.

SQL> commit;

Commit complete.

SQL> print rc;

OWNER                          OBJECT_NAME                                      
------------------------------ ------------------------------                   
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE            
------------------------------ ---------- -------------- ------------------     
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S                             
-------- -------- ------------------- ------- - - -                             
SYS                            FILE$                                            
                                       17             17 TABLE                  
07/03/01 07/03/01 2001-03-07:17:55:02 VALID   N N N                             
                                                                                
...





I understood that when we delete rows, they are moved to rollback segment and when we print the cursor, it is rebuilding rows from the rollback segments.

Instead of deleting the rows, I dropped the table but the printing of the cursor is still working : 

SQL> create table laly as select * from all_objects;

Table created.

SQL> select count(*) from laly;

  COUNT(*)                                                                      
----------                                                                      
      3566                                                                      

SQL> variable rc refcursor;
SQL> begin
  2  open :rc for select * from laly;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> drop table laly;

Table dropped.

SQL> print rc;

OWNER                          OBJECT_NAME                                      
------------------------------ ------------------------------                   
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE            
------------------------------ ---------- -------------- ------------------     
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S                             
-------- -------- ------------------- ------- - - -                             
SYS                            FILE$                                            
                                       17             17 TABLE                  
07/03/01 07/03/01 2001-03-07:17:55:02 VALID   N N N                             
                                                                                
...

SQL> select count(*) from laly;
select count(*) from laly
                     *
ERROR at line 1:
ORA-00942: table or view does not exist 

My questions :
1. shouldn't there be a lock preventing me from dropping the table when there is an opened cursor based on it
2. from where is the cursor getting the rows as for me the drop table moved nothing at all to the rollback segments.


Best regards,

Laly.