Hi friends,
I am trying to use a type, which is defined at package level, in a pipelined table function. But it fails saying "PLS-00630: pipelined functions must have a supported collection return type".
My DB version is Oracle 12.2. I assume that in this version, the packaged types are supported in function return type. But why it fails? Could you please suggest?
--Package that contains types.
CREATE OR REPLACE PACKAGE pkg_type AS
TYPE ty_emp_result IS RECORD (
empno NUMBER(4),
ename VARCHAR2(10)
);
TYPE tb_emp_result IS TABLE OF ty_emp_result
INDEX BY pls_integer;
TYPE tb_emp IS TABLE OF emp%rowtype
INDEX BY pls_integer;
END pkg_type;
/
--The pipelined function.
CREATE OR REPLACE FUNCTION tab_fun
RETURN pkg_type.tb_emp_result
pipelined
AS
l_tb_emp pkg_type.tb_emp;
l_row_emp pkg_type.ty_emp_result;
BEGIN
SELECT e.*
bulk collect into l_tb_emp
FROM emp e
where e.deptno = 20;
FOR i IN 1 .. l_tb_emp.count
loop
l_row_emp.empno := l_tb_emp(i).empno;
l_row_emp.ename := l_tb_emp(i).ename;
PIPE ROW(l_row_emp);
END LOOP;
END tab_fun;
/