-- PackageCREATE OR REPLACEPackage test_141213 isTYPE type_ref isRecord (enameVARCHAR2( -), Work_cityVARCHAR2( -), SAL Number(Ten)); TYPE T_type_ref is TABLE ofType_ref; FUNCTIONRetrieve (V_namevarchar2)RETURNt_type_ref pipelined;ENDtest_141213;--Package BODYCREATE OR REPLACEPackage BODY test_141213 is FUNCTIONRetrieve (V_namevarchar2)RETURNt_type_ref pipelined isCur_type_ref Type_ref; Type ref_cur_variable isREFcursor; Cur_variable ref_cur_variable; --rec_emp Type_ref%rowtype;V_sqlvarchar2( -) := 'Select E.ename, D.loc as Work_city, e.sal from Scott.emp E, scott.dept d where E.deptno = D.deptno'; BEGIN OpenCur_variable forV_sql; LoopFetchcur_variable intoCur_type_ref; Exit whenCur_variable%NotFound; Dbms_output.put_line (cur_variable%RowCount || ' -' ||Cur_type_ref.ename|| ' ' ||Cur_type_ref.work_city|| ' ' ||cur_type_ref.sal); PIPEROW (CUR_TYPE_REF); EndLoop; Closecur_variable; RETURN; END;ENDtest_141213;--Test RetrieveSelect * from Table(Test_141213.retrieve ('Robin'));
Oracle plsql Demo-18.01 Pipeline function[Query fragmented fields make a list pipeline return]