Hi guys
Since my last post here regading Oracle Collections (multidimensional ones) I've advanced a lot with my program.
A new issue arose today : how to use the function TABLE() with nested table .
I wrote the following code in Oracle DB 11g 11.2.0.1
(sorry I forgot to copy the package Specification) , it follows now :
create or replace PACKAGE PKG_TESTCOLLECTION IS
TYPE T_rec is RECORD (code number, name varchar(10) );
TYPE T_table is TABLE OF T_rec;
PROCEDURE PR_SHOWDATA;
END;
CREATE OR REPLACE PACKAGE BODY PKG_TESTCOLLECTION IS
PROCEDURE PR_SHOWDATA IS
v_nestable1 T_table := T_table();
v_nestable2 T_table := T_table();
v_index NUMBER;
BEGIN
for v_index IN 1..10
loop
v_nestable1.Extend;
v_nestable1(v_index).code := v_index;
dbms_output.put_line('Collection Index : ' || v_index || ' Content : ' || v_nestable1(v_index).code);
end loop;
17 select * BULK COLLECT INTO v_nestable2 from TABLE(v_nestable1) ORDER BY code DESC; /* I got an ERROR in this line 17 */
20 For rec IN ( select * from TABLE(v_nestable1) ORDER BY code DESC) /* I also got an ERROR in this line 20 */
LOOP
dbms_output.put_line('Nested Table Element : ' || rec.code);
END LOOP;
END PR_SHOWDATA;
END;
1 BEGIN /* Main Program */
2 PKG_TestCollection.pr_showdata;
3 END;
==> I got the below error , in the identified 17 and 20 lines with coments , above
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "RIC.PKG_TESTCOLLECTION", line 17
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 do appreciate any help on this issue !
Thank you in advance.
Ricardo.