ORA-22905: cannot access rows from a non-nested table item

gaurav picture gaurav · Dec 13, 2011 · Viewed 8.7k times · Source
CREATE OR REPLACE TYPE myObjectFormat 
AS OBJECT
(
 A   VARCHAR2(200),
 B   INTEGER,
 C   INTEGER
)
/

CREATE OR REPLACE TYPE myTableType
   AS TABLE OF myObjectFormat ;
/

CREATE OR REPLACE PACKAGE demo4
AS
  FUNCTION f1(p_abc_tab IN myTableType) RETURN myTableType PIPELINED;
 END;
/

CREATE OR REPLACE PACKAGE BODY demo4 AS
FUNCTION f1(p_abc_tab IN myTableType) RETURN myTableType PIPELINED IS
BEGIN
FOR i in p_abc_tab.first .. p_abc_tab.last
 LOOP
   PIPE ROW (myObjectFormat(p_abc_tab(i).a,p_abc_tab(i).b,p_abc_tab(i).c));
 END LOOP;
 RETURN;
 END;
END;
 / 
create or replace function demo3
 return TYPES.RETURN_CUR
IS

abc_tab myTableType:=myTableType();
abc_cur TYPES.RETURN_CUR;


begin
abc_tab.EXTEND;
abc_tab (1)      := myObjectFormat('ac',1,2);
 abc_tab.EXTEND; 
abc_tab (2)      := myObjectFormat('acfc',1,2);

open abc_cur for select * from table(demo4.f1(abc_tab)) ;

return abc_cur;

 end;

select demo3() from dual;

I am getting the following erro

ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at "T416493.DEMO3", line 15

I want to return thr cursor from a function to front end java. I am using oracle as RDBMS ,what might be the problem with this code.

Answer