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?