Hi All,
I want to use a pipeline function in select as shown below but facing issue can you please suggest me to do it correctly.
CREATE TYPE t_row AS OBJECT (
empno VARCHAR2(10)
, ename VARCHAR2(10)
, deptno NUMBER
);
CREATE TYPE t_row_tab AS
TABLE OF t_row;
CREATE OR REPLACE FUNCTION pltbl_function (in_deptno NUMBER)
RETURN t_row_tab
PIPELINED
PARALLEL_ENABLE
AUTHID current_user
IS
lv_no_list t_row_tab;
BEGIN
SELECT
t_row(empno, ename, deptno)
BULK COLLECT
INTO lv_no_list
FROM emp WHERE deptno = in_deptno;
FOR i IN 1..lv_no_list.count
LOOP
PIPE ROW ( t_row(lv_no_list(i).empno,lv_no_list(i).ename, lv_no_list(i).deptno) );
END LOOP;
END pltbl_function;
select ename, empno from TABLE(pltbl_function(d.deptno))
FROM dept;