Link to home
Start Free TrialLog in
Avatar of chalie001
chalie001

asked on

hi can oracle function return two values

hi i have the following function i have to change it to return two values is it possible
function12.sql
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chalie001
chalie001

ASKER

i what to do something like this
CREATE OR REPLACE TYPE myemp AS OBJECT
 ( empno    number,
   ename    varchar2(10),
   job      varchar2(10),
   mgr      number,
   hiredate date,
   sal      number,
   comm     number,
   deptno   number
 );

 CREATE OR REPLACE TYPE myrectable AS TABLE OF myemp ;


    enter code here

CREATE OR REPLACE FUNCTION pipedata(p_min_row number, p_max_row number) RETURN myrectable PIPELINED IS
    v_obj myemp := myemp(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
 BEGIN
   FOR e IN (select *
             from (
                   select e.*
                         ,rownum rn
                   from (select * from emp order by empno) e
                  )
             where rn between p_min_row and p_max_row)
   LOOP
     v_obj.empno    := e.empno;
     v_obj.ename    := e.ename;
     v_obj.job      := e.job;
     v_obj.mgr      := e.mgr;
     v_obj.hiredate := e.hiredate;
     v_obj.sal      := e.sal;
     v_obj.comm     := e.comm;
     v_obj.deptno   := e.deptno;
     PIPE ROW (v_obj);
   END LOOP;
   RETURN;
 END;

SQL> select * from table(pipedata(1,5));

Open in new window

shareimprove this answer
And where is a problem ?
thanks i use the regexp_replace function
This is correct answer.