Forum Stats

  • 3,769,471 Users
  • 2,252,969 Discussions
  • 7,875,044 Comments

Discussions

can functions return multiple values?

495617
495617 Member Posts: 18
edited Mar 22, 2011 10:38PM in SQL & PL/SQL
Can a pl/sql function return more than one value through OUT paramaters? If so, is it advisable to use it?
2684010Hawk333San The Explorer
«1

Comments

  • Chaitanya.S.S.K
    Chaitanya.S.S.K Member Posts: 1,249
    funtion returns only one value at a time. function parameters are always IN, no OUT is possible
  • 457512
    457512 Member Posts: 91
    A pl/sql function cannot return a multiple values, even you can't use out parameter in functions . out parameters is valid in only procedures, in procedures you can rreturn multiple values using out parameter for each returned value

    Thanks
    Raj Deep.A
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    You certainly CAN define functions with OUT parameters. It is generally not advisable to do so, however. Functions with OUT parameters, for example, cannot be called from SQL, among other useful things.

    If you find yourself wanting a function that returns multiple values, you would generally want to either
    - create a procedure that has multiple OUT parameters
    - create a function that returns an object type that encapsulates the values you want to return

    Justin
    Hawk333San The Explorer
  • 495617
    495617 Member Posts: 18
    Functions can have an out parameter... try executing this one...

    1 create or replace function f1(name1 in varchar2, age1 out number)
    2 return number
    3 is
    4 begin
    5 select age into age1 from mytab where name=name1;
    6 return 1;
    7* end;
  • Chaitanya.S.S.K
    Chaitanya.S.S.K Member Posts: 1,249
    thank you Cave, is there any scenario why/where we use functions with OUT parameters.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    I can't think of a situation where it would be a good idea to have a function with OUT parameters, no.

    Justin
  • Chaitanya.S.S.K
    Chaitanya.S.S.K Member Posts: 1,249
    thanks again. If we can achieve any scenario without OUT parameters of functions, as well as when functions with OUT and IN OUT parameters are avoidable, oracle can remove this feature probably in future versions
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    I would seriously doubt that Oracle would ever remove this fuctionality. Too much production code likely depends on it.

    I would suspect as well that it's easier on the parsing side to permit both procedures and functions use the same code for parsing argument lists.

    Justin
  • Onenessboy
    Onenessboy Member Posts: 625
    You can return objects.


    SQL> create type my_type as object (a number, b varchar2(10));
    2 /

    Type created.

    SQL> create or replace function my_function(a number, b varchar2) return my_type
    2 as
    3 my_obj my_type;
    4 begin
    5 my_obj := my_type(a,b);
    6 return my_obj;
    7 end;
    8 /

    Function created.

    SQL> select
    2 x.obj.a id,x.obj.b name from
    3 (
    4 select my_function(1,'kamal') obj from dual
    5 union all
    6 select my_function(2,'AAAA') obj from dual
    7 union all
    8 select my_function(3,'BBBB') obj from dual
    9 ) x;

    ID NAME
    ---------- ----------
    1 kamal
    2 AAAA
    3 BBBB

    SQL>
  • 208428
    208428 Member Posts: 34
    i have the same problem and i use a another solution

    i was used pipelined
    CREATE OR REPLACE PACKAGE X IS
    TYPE TP IS TABLE OF TABLE%ROWTYPE;
    FUNCTION RET (A NUMBER, B NUMBER, C NUMBER) RETURN TP PIPELINED;
    END X;
This discussion has been closed.