CODEWITHSUNDEEP
oracle-databaseplsql
Vipul Tyagi
Vipul Tyagi

Reputation: 35

Object No longer exists

I have a procedure by which i am returning a cursor.

create or replace procedure pkg_test(cur out sys_refcursor) is
begin   
  insert into tb_test values(1);
  insert into tb_test values(2);  
  insert into tb_test values(3);

  open cur for
  select * from tb_test;

  delete  from tb_test;

  commit;  
end pkg_test;

This is working fine.

now i have created a global temporary table for some performance issue like below. 

create global temporary table tb_test_GTT (deal_id int)
on commit delete rows;

create or replace procedure pkg_test(cur out sys_refcursor) is

begin    
  insert into tb_test_GTT values(1);   
  insert into tb_test_GTT values(2);  
  insert into tb_test_GTT values(3);

  open cur for
  select * from tb_test_GTT;

  delete  from tb_test_GTT;

  commit;
end pkg_test;

Now when i am trying to fetching the data from cursor i am getting below error:-

ORA-08103: object no longer exists.

I can correct this error by adding on commit preserve rows but i want to know the reason.

Upvotes: 2

Views: 1525

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30595

After commit your data no longer exists. This is how Temporary Tables works in Oracle.

Cursor is basically reference to table. You cannot return non-existent object, hence error occurs because referenced data is no longer there.

You may consider to return Table Type object since this approach stores data in-memory.

Reference from the Official Documentation says:

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs have the following characteristics:

  • A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.

  • A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.

Upvotes: 2

Related Questions