944768 wrote:That is NOT a valid reason. You do not migrate to move from one environment to another, quickly. You migrate from one environment to another to make things BETTER.
Thing is , we have migrated so do not right now want to change any thing due less time.
and we got information that noraml functions uses PGA memory so we decided to use this.Wrong information. If PL/SQL functions were "bad", then why did Oracle not simply discontinued function support - and only provided support for pipeline functions?
-- type to match emp record create or replace type emp_scalar_type as object (EMPNO NUMBER(4) , ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2) ) / -- table of emp records create or replace type emp_table_type as table of emp_scalar_type / -- pipelined function create or replace function get_emp( p_deptno in number ) return emp_table_type PIPELINED as TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; l_rec emp%rowtype; begin open emp_cv for select * from emp where deptno = p_deptno; loop fetch emp_cv into l_rec; exit when (emp_cv%notfound); pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename), l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) ); end loop; return; end; / select * from table(get_emp(20))
rp0428 wrote:No requirement was stated. A solution, "joining" cursors, was decided on - and the problem left unstated.
And how is this better:
select * from table(get_emp(20))
open emp_cv for select * from emp where deptno = p_deptno;
Because the first satisfies this requirement and the second doesn't.