ramesh_348401

 Profile Answers by 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

krishnaindia2007

 Profile Answers by 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

 Profile Answers by 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.

KD09714

 Profile Answers by KD09714 Questions by 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).

gtomar

 Profile Answers by 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

ananth.oracle

 Profile Answers by 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.

javedans

 Profile Answers by 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.

sen_sam86

 Profile Answers by 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