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
Oracle DatabaseDatabasesMongoDB

Avatar of undefined
Last Comment
Helena Marková

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Helena Marková

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Helena Marková

And where is a problem ?
chalie001

ASKER
thanks i use the regexp_replace function
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Helena Marková

This is correct answer.