Oracle Function that returns multiple values

[naga5566]

Hi All,

This might be a silly question but..., I am trying to write a function in oracle that returns multiple values.I looked at some of the forums and online docs and found out that functions in oracle returns single value. Is there a way we can write this to return multipl values.If yes,can someone post the syntax of the function.I have some thing like this

Create Function Fun(MinDate In Date,MaxDate,WeekDays Out number,WeekEnds out number) return as number
is
Begin

--My logic goes here to calculate WeekDays and WeekEnds.
--say assigned the values to WeekDays and WeekEnds
WeekEnds:= 3;
WeekDays:= 7;

Return WeekEnds,WeekDays --Does this Work ?

End

 

[Thargy]

naga,

Functions do only return one value. If you want to commit a programming crime, you can create a function with an out parameter, but it's not generally thought to be a good idea.

As an alternative, have the function return a sysrefcursor, which will enable it to return a large amount of data. You still get only one value (i.e. the refcursor) but that value may point to a large amount of data.

Regards

Tharg

Grinding away at things Oracular

 

[Dagon]

You can also return a type, which contains multiple fields. There would be a bit more work for this, as you would have to use a package.

create or replace package pkg_test as
   type rectype is record (a number, b number);
   function get_val return rectype;
end;
/

create or replace package body pkg_test as
   function get_val return rectype is
    rec rectype;
   begin
     rec.a := 5;
	 rec.b := 7;
	 return rec;
   end;
end;
/

declare
  v_result pkg_test.rectype;
begin
  v_result := pkg_test.get_val;
  dbms_output.put_line(v_result.a);
  dbms_output.put_line(v_result.B);
END;
/

 

[SantaMufasa]

Since all of your RETURN items are of datatype NUMBER, the other option that obeys good form is to build a function to which you pass an argument that tells the function which of the data items you want it to return. (I recommend a PACKAGED FUNCTION so that there is never any re-invocation or re-running of any code to achieve your three values and (the big payoff of a package)-- the three values are persistent.) Here is my suggestion (that does not involve refcursors or multi-expression TYPEs:

create or replace package pkg_test as
    Function Fun(MinDate Date, MaxDate Date, Which_var varchar2) return number;
end;
/

Package created.

create or replace package body pkg_test as
   Function Fun(MinDate Date, MaxDate Date, Which_var varchar2) return number is
   WeekEnds  number := 3;
   WeekDays  number := 7;
   begin
     if    which_var = 'WE' then return WeekEnds;
     elsif which_var = 'WD' then return WeekDays;
     else  return null;
     end if;
   end;
end;
/

Package body created.

select pkg_test.fun(sysdate,sysdate,'WD')Weekdays
      ,pkg_test.fun(sysdate,sysdate,'WE')WeekEnds
  from dual;

WEEKDAYS   WEEKENDS
-------- ----------
       7          3

Let us know if this provides additional insight.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Also, if you want to return multiple values, why do you want to use a function. Simply use a procedure and you can have as many return values as you want.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

...why do you want to use a function?

It may be that Naga is invoking the function (as I did) from a SQL statement rather than a PL/SQL venue...Can't invoke a procedure directly from a SQL statement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[naga5566]

I want to use this procedure/function in informatica mapping, where i will be passing the From_Dte,To_dte
and this proc/func will return the total weekdays and weekends.Ofcourse there is some more logic i ll be putting in this fuction to calculate the wd's and we's.

 

[naga5566]

SantaMufasa,

I create a package just as you suggested, but it runs for ever. Here is the code.
************************************************
Create or replace package WDWE_Rule as
Function getWdWe(
from_dt Date,
closedate DATE,
total_daysNUMBER,
WD_WE Varchar2) return number;
end WDWE_Rule;


Create or replace package body WDWE_Rule as

Function getWdWe(
from_dt Date,
closedate DATE,
total_days NUMBER,
WD_WE Varchar2)
return number
IS
temp_dats NUMBER;
to_dt DATE;
wd NUMBER;
we NUMBER;
savepointid NUMBER;
BEGIN

LOOP
temp_dats := 0;
to_dt := from_dt + total_days- 1;
wd := 0;
we := 0;


WHILE temp_dats < total_days
LOOP
BEGIN
IF ( TO_NUMBER (TO_CHAR (from_dt + temp_dats, 'D')) > 1
AND TO_NUMBER (TO_CHAR (from_dt + temp_dats, 'D')) < 6
)
OR ( from_dt + temp_dats = to_dt
AND TO_NUMBER (TO_CHAR (from_dt + temp_dats, 'D')) = 6
AND to_dt >= TRUNC (closedate)
)
THEN
BEGIN
wd := wd + 1;
END;
ELSIF ( TO_NUMBER (TO_CHAR (from_dt + temp_dats, 'D')) = 1
OR TO_NUMBER (TO_CHAR (from_dt + temp_dats, 'D')) >= 6
)
OR ( TO_NUMBER (TO_CHAR (to_dt, 'D')) = 6
AND to_dt < TRUNC (closedate)
)
THEN
BEGIN
we := we + 1;
END;
END IF;

temp_dats := temp_dats + 1;
END;


END LOOP;
END LOOP;


Begin
if wd_we = 'WE' then return We;
elsif wd_we = 'WD' then return Wd;
else return null;
end if;
end;
END;
End WDWE_Rule;

 

[SantaMufasa]

Naga,

That is where the term "infinite loop" came from...I don't see any method of ever kicking out of your outer loop.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]