Home » SQL & PL/SQL » SQL & PL/SQL » Object does not exist or marked for delete (Oracle 10g)
Object does not exist or marked for delete [message #442239] Fri, 05 February 2010 17:02 Go to next message
kregan77
Messages: 21
Registered: January 2007
Junior Member
Hello, below is a simplified example of something I am trying to do, which involves a function that returns a ref cursor over a local table of records. I'm getting the error message "Object does not exist or marked for delete". I imagine it must have something to do with the table object going out of scope? but I'm not sure how to accomplish what I'm trying to do here... anybody have any tips for me?

SQL> create package objtest as
  2  type rc_t is ref cursor;
  3  type rec_t is record (
  4   a number,
  5   b number
  6  );
  7
  8  type list_t is table of rec_t;
  9  function testit return rc_t;
 10  end;
 11  /

Package created.

SQL> create or replace package body objtest as
  2  function testit return rc_t is
  3  i number;
  4  thelist list_t;
  5  rc rc_t;
  6  begin
  7    i := 0;
  8    while i < 5 loop
  9      if i = 0 then
 10        thelist := list_t();
 11      end if;
 12      thelist.extend;
 13      thelist(thelist.last).a := i;
 14      thelist(thelist.last).b := i + 5;
 15      i := i + 1;
 16    end loop;
 17    open rc for select * from table(thelist);
 18    return rc;
 19  end;
 20  end;
 21  /

Package body created.

SQL> set serveroutput on;
SQL> declare
  2    type rc_t is ref cursor;
  3    rc rc_t;
  4    therec objtest.rec_t;
  5  begin
  6    rc := objtest.testit;
  7    loop
  8     fetch rc into therec;
  9     exit when rc%notfound;
 10     dbms_output.put_line(therec.a||' '||therec.b);
 11    end loop;
 12  end;
 13  /
declare
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SDS.OBJTEST", line 17
ORA-06512: at line 6


SQL>


Re: Object does not exist or marked for delete [message #442240 is a reply to message #442239] Fri, 05 February 2010 18:28 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
rec_t and list_t need to created as SQL rather PL/SQL types (ie outside of the package using create type statements) for you to be able to use them in a ref cursor.
Re: Object does not exist or marked for delete [message #442251 is a reply to message #442239] Sat, 06 February 2010 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your version with 4 decimals?
In 10.2.0.4 Oracle clearly displays the error:
SQL> create or replace package objtest as
  2  type rc_t is ref cursor;
  3  type rec_t is record (
  4   a number,
  5   b number
  6  );
  7  
  8  type list_t is table of rec_t;
  9  function testit return rc_t;
 10  end;
 11  /

Package created.

SQL> create or replace package body objtest as
  2  function testit return rc_t is
  3  i number;
  4  thelist list_t;
  5  rc rc_t;
  6  begin
  7    i := 0;
  8    while i < 5 loop
  9      if i = 0 then
 10        thelist := list_t();
 11      end if;
 12      thelist.extend;
 13      thelist(thelist.last).a := i;
 14      thelist(thelist.last).b := i + 5;
 15      i := i + 1;
 16    end loop;
 17    open rc for select * from table(thelist);
 18    return rc;
 19  end;
 20  end;
 21  /

Warning: Package Body created with compilation errors.

SQL> 
SQL> sho err
Errors for PACKAGE BODY OBJTEST:
LINE/COL
---------------------------------------------------------------------
ERROR
---------------------------------------------------------------------
17/15
PL/SQL: SQL Statement ignored
17/29
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
17/35
PLS-00642: local collection types not allowed in SQL statement

But this is not the only modification you have to do in your code. You might modify it like:
SQL> create or replace type rec_t is object (
  2   a number,
  3   b number
  4  );
  5  /

Type created.

SQL> create or replace  type list_t is table of rec_t;
  2  /

Type created.

SQL> create or replace package objtest as
  2  function testit return sys_refcursor;
  3  end;
  4  /

Package created.

SQL> create or replace package body objtest as
  2  function testit return sys_refcursor is
  3  i number;
  4  thelist list_t := list_t();
  5  rc sys_refcursor;
  6  begin
  7    i := 0;
  8    while i < 5 loop
  9      thelist.extend;
 10      thelist(thelist.last) := rec_t(i, i+5);
 11      i := i + 1;
 12    end loop;
 13    open rc for select rec_t(a,b) from table(thelist);
 14    return rc;
 15  end;
 16  end;
 17  /

Package body created.

SQL> declare
  2    rc sys_refcursor;
  3    therec rec_t;
  4  begin
  5    rc := objtest.testit;
  6    loop
  7     fetch rc into therec;
  8     exit when rc%notfound;
  9     dbms_output.put_line(therec.a||' '||therec.b);
 10    end loop;
 11  end;
 12  /
0 5
1 6
2 7
3 8
4 9

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Sat, 06 February 2010 01:24]

Report message to a moderator

Re: Object does not exist or marked for delete [message #442495 is a reply to message #442239] Mon, 08 February 2010 09:22 Go to previous message
kregan77
Messages: 21
Registered: January 2007
Junior Member
Thanks for the help guys, much appreciated.

PS - I was using 11.1.0.6.0 client connecting to a 11.1.0.7.0 database.

[Updated on: Mon, 08 February 2010 09:23]

Report message to a moderator

Previous Topic: Parsing comma separated values into variables. (merged 6)
Next Topic: how to use USING clause for dynamically appended where clauses
Goto Forum:
  


Current Time: Mon Sep 26 14:34:54 CDT 2016

Total time taken to generate the page: 0.09073 seconds