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

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.

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