Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

selecting from nested table produces ORA-21700

doug8294May 19 2010 — edited May 19 2010
On Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
CREATE OR REPLACE PACKAGE testpack AS
  TYPE DateType IS RECORD (date_time TIMESTAMP);
  TYPE DateTableType IS TABLE OF DateType;
  PROCEDURE testproc;
END;


CREATE OR REPLACE PACKAGE BODY testpack AS

  PROCEDURE testproc
  AS
   times DateTableType := DateTableType();
  begin

    times.extend;
    times(times.count).date_time := to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS');
  
    FOR c1 IN ( select distinct date_time from table(times)
                  order by date_time) 
    LOOP
      DBMS_OUTPUT.PUT_LINE(c1.date_time);
    END LOOP; 
  end;
END;

BEGIN
  testpack.testproc;
END;

Produces:

ORA-21700: object does not exist or is marked for delete
ORA-06512: at "XXXXXXX.TESTPACK", line 11
ORA-06512: at line 2
21700. 00000 -  "object does not exist or is marked for delete"
*Cause:    User attempted to perform an inappropriate operation to
           an object that is non-existent or marked for delete.
           Operations such as pinning, deleting and updating cannot be
           applied to an object that is non-existent or marked for delete.
*Action:   User needs to re-initialize the reference to reference an
           existent object or the user needs to unmark the object.
I have many timestamps in a nested table. My goal is to sort them, and only get unique timestamps. What is the appropriate way to select from a nested table?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 16 2010
Added on May 19 2010
2 comments
1,734 views