0
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.

MT0
  • 143,790
  • 11
  • 59
  • 117
gaurav
  • 345
  • 2
  • 9
  • 20
  • 1
    possible duplicate of [how to nested table in cursor](http://stackoverflow.com/questions/8485546/how-to-nested-table-in-cursor) – Ollie Dec 13 '11 at 15:41
  • Please find answer in the below link: http://stackoverflow.com/questions/8485546/how-to-nested-table-in-cursor – gaurav Dec 14 '11 at 05:54
  • I replaced TYPES.RETURN_CUR with SYS_REFCURSOR and it worked for me on Oracle 11g. – jva Jul 25 '12 at 11:34

0 Answers0