Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Out Parameters Function in Select Statement - Oracle (Oracle 11.2.0.3.0)
Multiple Out Parameters Function in Select Statement - Oracle [message #677939] Thu, 24 October 2019 23:35 Go to next message
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 #677940 is a reply to message #677939] Fri, 25 October 2019 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot use a function or procedure with an OUT parameter in SQL.

Re: Multiple Out Parameters Function in Select Statement - Oracle [message #677947 is a reply to message #677940] Fri, 25 October 2019 03:59 Go to previous messageGo to next message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
Thanks Dear. any other way out?

[Updated on: Fri, 25 October 2019 03:59]

Report message to a moderator

Re: Multiple Out Parameters Function in Select Statement - Oracle [message #677950 is a reply to message #677947] Fri, 25 October 2019 04:37 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You could return an object type as shown in AskTOM , but you should have good reasons to do so.
Re: Multiple Out Parameters Function in Select Statement - Oracle [message #677951 is a reply to message #677947] Fri, 25 October 2019 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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

Re: Multiple Out Parameters Function in Select Statement - Oracle [message #677952 is a reply to message #677951] Fri, 25 October 2019 04:47 Go to previous message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
Michel Cadot - You are always helping. thanks a lot dear.
Previous Topic: Cursor throwing error while passing to dbms_stats.gather_schema_stats
Next Topic: ANALYZE TABLE STATEMENT
Goto Forum:
  


Current Time: Thu Mar 28 08:10:54 CDT 2024