How to get a function to Return multiple values.

[snowcold]

Is there a way that I get a function to return multiple values?

Thanks

 

[snowcold]

I guess I want a procedure rather than a function......

 

[LKBrwnDBA]

Use a procedure and define parameters you wish to return as 'OUT' or 'IN OUT':

Create or Replace Procedure MyProc 
(Par1 IN Varchar2, Par2 IN OUT Number, Par3 OUT Date)
Is ...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[snowcold]

Can I call a procedure just like I would a function?

what do you mean by "'OUT' or 'IN OUT':"

thanks

 

[LKBrwnDBA]

You cannot call a procedure like a function (vg. in a condition like fx(a) = 'Foo'), but you could have a function with IN/OUT parameters -- not advisable.

IN (or null) means the parameter is Input only.
OUT means the parameter is return value ONLY.
IN OUT means the parameter is both input and return value.\





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[snowcold]

see, I have a view that is being created. Inside of this particular view there a functions being called. I have just came across a scenerio where these functions may have to return multiple values.
I still need to use these views, or I may have to create new views without using these functions...

I think I may want to stay away from procedures...

thanks

 

[sem]

You may create function returning some complex (object) type.

Regards, Dima

http://seminihin.narod.ru

 

[snowcold]

Sem,

do you mind elaborating on your post..?

I am sort of stuck on what to do.

thanks

 

[SantaMufasa]

Snow,

Perhaps we can be more helpful if you explain the function that returns a variable number of RETURN values.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

create or replace type numbers as object(number1 number, number2 number)
/

create function getNumbers return numbers
is
result numbers := numbers(1,2);
begin
  return result;
end;
/

create or replace view viewNumbers as select getNumbers from dual
/

select s.getNumbers.number1 number1, s.getNumbers.number2 number2 from viewNumbers s
/

Regards, Dima

http://seminihin.narod.ru

 

[snowcold]

sure,

We have groups of plastic components which we call lots. These components are normally made from 2 cavities, a top and bottom. But occassionaly, as I just found out, the components can be made from 2 different top cavities. The function returns data on these cavities based on the lot of the plastic component. I am using this to create a view which will then be used on a report

My view is created like this:

CREATE OR REPLACE VIEW REPORT_INFO ( THIS, THAT
ANOTHER, TOP_CAVITY, BOTTOM_CAVITY ) AS
SELECT this, 
that,
another,
(getcavityinfo(Lot#),'Top') AS TOP_CAVITY,
(getcavityinfo(Lot#),'Bottom') AS BOTTOM_CAVITY
FROM
Table1,
Table2
WHERE
criteria.......

example:
when a lot is made from 2 cavities, I have multiple records:

cavitytype cavitynum   size  location  time
--------------------------------------------------
Top         cavity1    9.6   front    10:25:31
Top         cavity2    9.63  front    10:27:05

 

[SantaMufasa]

Snow,

Check out Sem's clever solution. I recommend it.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[LKBrwnDBA]

Or also, why not concatenate the return values? something like this:

Create Or Replace Function Getcavityinfo
(P_Lotnum Number, P_Cav_Type Varchar2)
Return Varchar2 Is
V_Cavtxt Varchar2(40):='';
V_Sep Varchar2(1):='';
Begin
  For X In (Select Cavitynum
              From Lot_Cavity_Table
             Where Lot_Num = P_Lotnum
               And Cav_Type = P_Cav_Type)
  Loop
    V_Cavtxt := V_Cavtxt||V_Sep||X.Cavitynum;
    V_Sep:=', ';
  End Loop;
  Return V_Cavtxt;
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb