It's my written Function for return loan scheme name from oracle database
i think it's help you to solve your problem:
CREATE OR REPLACE FUNCTION GETACNAME (Vscheme IN VARCHAR2 , Vacno IN NUMBER)
RETURN VARCHAR2 IS
VAcName VARCHAR2(200);
AcNoType VARCHAR2(2);
BEGIN
SELECT S_ACNOTYPE INTO AcNoType FROM SCHEMAST WHERE S_APPL = Vscheme ;
If AcNoType = 'TD' Or AcNoType = 'SB' Or AcNoType = 'CA' Or AcNoType = 'AG' Or AcNoType = 'GS' Or AcNoType = 'IV' Or AcNoType = 'LK' Then
SELECT AC_NAME Into VAcName FROM DPMaster
WHERE Ac_AcNoType = AcNoType
And AC_TYPE = Vscheme
And AC_NO = Vacno ;
End If;
RETURN VAcName ;
END GETACNAME ;