Multiple Out Parameters Function in Select Statement - Oracle [message #677939] |
Thu, 24 October 2019 23:35 |
myclassic
Messages: 136 Registered: December 2006 Location: Pakistan
|
Senior Member |
|
|
create or replace function myFunc(a in number, b in number, c out number) return varchar2 is
Result varchar2(100);
begin
c:= a+b;
result := 'Success';
return(Result);
exception when others then
result := 'Failure';
return(Result);
end myFunc;
I want to use this function in Select statement.
any help please.
|
|
|
|
|
|
Re: Multiple Out Parameters Function in Select Statement - Oracle [message #677951 is a reply to message #677947] |
Fri, 25 October 2019 04:39 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The correct way is to return c and let the exception go:
create or replace function myFunc(a in number, b in number) return number is
begin
return a+b;
end myFunc;
/
The caller handles the exception.
The way for a function to return several values is to return a record:
SQL> create or replace type myType is object (
2 plus number,
3 minu number
4 )
5 /
Type created.
SQL> show errors type myType
No errors.
SQL> create or replace function myFunc(a in number, b in number) return myType is
2 begin
3 return myType (a+b,a-b);
4 end myFunc;
5 /
Function created.
SQL> show errors
No errors.
SQL> select myFunc(1, 2) res from dual
2 /
RES(PLUS, MINU)
-----------------------------------------------------------------------------------
MYTYPE(3, -1)
1 row selected.
SQL> select r.res.plus plus, r.res.minu "MINUS"
2 from (select myFunc(1, 2) res from dual) r
3 /
PLUS MINUS
---------- ----------
3 -1
1 row selected.
[Updated on: Fri, 25 October 2019 07:53] Report message to a moderator
|
|
|
|