Function return more than one value

Hi,

My questions is " Can function return more than one value".
I have gone through the all answers relevant to my question was been posted earlier. But all answers are confusing me.
Few people said directly that function can't return more than one value and few are telling that function can return more that one value using OUT parameter or ref cursor.
My understanding is if out can't use as a parameter in function how we can return more than one vale.

Can anyone clear me with exact program example.
it would really favour for me if any any reply soon as I am in urged need of answer.

Please reply ASAP.
Regards,
ORA

Questions by satyam_Ora   answers by satyam_Ora

Showing Answers 1 - 8 of 8 Answers

ramesh_348401

  • May 22nd, 2008
 

you define that function must return a char value because. it max limit. chop all variable in that variable with remember the position of each value width. return that value and use substr function as per ur reqd.

ramesh

  Was this answer useful?  Yes

krishnaindia2007

  • May 28th, 2008
 

As per oracle documentation
The main purpose of a function is to take zero or more arguments and return sigle value.
Avoid using OUT  and INOUT parameter modes with  functions.
It is a poor programming practice to return more than one value using functions.

But we can return more than one value using out parameter as follows

DECLARE
A NUMBER := 10;
B NUMBER ;
C NUMBER ;
D NUMBER ;
FUNCTION TEST_FUN (A IN NUMBER, B  OUT NUMBER, C IN OUT NUMBER) RETURN NUMBER IS
D NUMBER;
BEGIN
B := A;
C := B + A;
D := A + B + C;
RETURN (D);
END ;
BEGIN
D := TEST_FUN(A,B,C);
DBMS_OUTPUT.PUT_LINE(' A =' || A);
DBMS_OUTPUT.PUT_LINE(' B =' || B);
DBMS_OUTPUT.PUT_LINE(' C =' || C);
DBMS_OUTPUT.PUT_LINE(' D =' || D);
END;


Using REF CURSOR we can return objects as return type. Here is example
DECLARE
TYPE EMP_REFCUR IS REF CURSOR;
EMP_CURVAR  EMP_REFCUR;
TYPE EMP_REC IS RECORD
(
V_EMPNO EMP.EMPNO%TYPE,
V_ENAME EMP.ENAME%TYPE,
V_JOB   EMP.JOB%TYPE,
V_SAL   EMP.SAL%TYPE
);
EMP_RECVAR EMP_REC;
FUNCTION TEST_FUN (V_EMPNO IN EMP.EMPNO%TYPE) RETURN EMP_REFCUR IS
BEGIN
OPEN EMP_CURVAR FOR SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE EMPNO = V_EMPNO;
RETURN(EMP_CURVAR);
END ;
BEGIN
EMP_CURVAR := TEST_FUN(7369);
FETCH EMP_CURVAR INTO EMP_RECVAR;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS ' || EMP_RECVAR.V_ENAME);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DESIGNATION IS ' || EMP_RECVAR.V_JOB);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE SALARY IS ' || EMP_RECVAR.V_SAL);
END;

I hope now it is clear to you.

Muskaan21priya

  • Jun 4th, 2008
 

Its depends upon the scenario. as we all knew that function must returns one value and this true. If u want to return more than one value through function you will have to use OUT parameter and record type and REF cursor, it will return return more than one value. Please let me know its clear to u or not. I will give explain you by example.

  Was this answer useful?  Yes

KD09714

  • Jun 8th, 2008
 


In addition to all above answers,
i wanted to add one more clarifications that

we should not use OUT and IN OUT parameter in the functions supposed to be called from sql expressions. that's where the people used to say we cant use out parameter in functions (called from sql expressions).

  Was this answer useful?  Yes

gtomar

  • Aug 7th, 2008
 

Hi,

First of all I want to make it clear that function must return a value but also can return more than one value at a time.

There are  two ways to return more than one value from function:-

1) you can implement this by passing out parameters in function. If you are using out parameter then you can not excute function through select statement.

for ex:-

Create or replace function func1
(id1      IN     <datatype1>,
id2      OUT     <datatype1>,
id3      OUT     <datatype1>,
id4      OUT     <datatype1>,
id5      OUT     <datatype1>,
id6      OUT     <datatype1>,
.....
)
return <datatype>
AS

BEGIN
--body of procedure

END func1;


Calling function from any block

BEGIN
.....
var1= func1(var2,var3,var4,var5,var6,var7,......);
....
END;


2) Other way is to use reference cursor .

for ex :-

DECLARE
A NUMBER := 10;
B NUMBER ;
C NUMBER ;
D NUMBER ;
FUNCTION TEST_FUN (A IN NUMBER, B  OUT NUMBER, C IN OUT NUMBER) RETURN NUMBER IS
D NUMBER;
BEGIN
B := A;
C := B + A;
D := A + B + C;
RETURN (D);
END ;
BEGIN
D := TEST_FUN(A,B,C);
DBMS_OUTPUT.PUT_LINE(' A =' || A);
DBMS_OUTPUT.PUT_LINE(' B =' || B);
DBMS_OUTPUT.PUT_LINE(' C =' || C);
DBMS_OUTPUT.PUT_LINE(' D =' || D);
END;


Using REF CURSOR we can return objects as return type. Here is example


DECLARE
TYPE EMP_REFCUR IS REF CURSOR;
EMP_CURVAR  EMP_REFCUR;
TYPE EMP_REC IS RECORD
(
V_EMPNO EMP.EMPNO%TYPE,
V_ENAME EMP.ENAME%TYPE,
V_JOB   EMP.JOB%TYPE,
V_SAL   EMP.SAL%TYPE
);
EMP_RECVAR EMP_REC;
FUNCTION TEST_FUN (V_EMPNO IN EMP.EMPNO%TYPE) RETURN EMP_REFCUR IS
BEGIN
OPEN EMP_CURVAR FOR SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE EMPNO = V_EMPNO;
RETURN(EMP_CURVAR);
END ;
BEGIN
EMP_CURVAR := TEST_FUN(7369);
FETCH EMP_CURVAR INTO EMP_RECVAR;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS ' || EMP_RECVAR.V_ENAME);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DESIGNATION IS ' || EMP_RECVAR.V_JOB);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE SALARY IS ' || EMP_RECVAR.V_SAL);
END;

Thanks,
Gunjan

  Was this answer useful?  Yes

ananth.oracle

  • Mar 9th, 2009
 

A Function can return one value at a time. If you want to return more than one value at a time from function then you should be using table functions.

  Was this answer useful?  Yes

javedans

  • Jul 8th, 2009
 

Its a universal truth that function can return only one value, but there are some tricks by using that you can get more than one value , while function returning only one value.

When you define function then you must define type of return statement like Varchar2, Number or Date

CREATE OR REPLACE FUNCTION A RETURN NUMBER;

Similar way if you define return type any existing record type or rowtype then while returning one value, you can get more one value as output.

CREATE OR REPLACE FUNCTION A RETURN emp%rowtype;

in this case you return rentire row of emp table which may contain any number of column.

  Was this answer useful?  Yes

sen_sam86

  • Jul 23rd, 2009
 

Yes this can be possible by TABLE FUNCTION

STEP - 1
    CREATE TYPE ex_tabl_fun_obj AS OBJECT (emp_name varchar2(20), emp_id NUMBER(2));

STEP - 2

    CREATE TYPE ex_tabl_fun_tbl AS TABLE OF ex_tabl_fun_obj;

STEP - 3

     CREATE FUNCTION get_all_objects (p_emp_sal NUMBER)
     RETURN ex_tabl_fun_tbl AS
           v_tab ex_tabl_fun_tbl := ex_tabl_fun_tbl();

     BEGIN
          
            FOR  i in (SELECT emp_sal, emp_id FROM emp_tabl WHERE emp_sal > emp_sal)

           LOOP
           v_tab EXTEND;
           v_tab(v_tab.LAST) := ex_tabl_fun_obj(i.emp_sal, i.emp_id);
           END LOOP;
           RETURN v_tab;
     
      END;

hope this is helpful

thank
senthil

 
        


   

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions