This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Mar 22, 2011 7:38 PM by 844722 RSS

can functions return multiple values?

495617 Newbie
Currently Being Moderated
Can a pl/sql function return more than one value through OUT paramaters? If so, is it advisable to use it?
  • 1. Re: can functions return multiple values?
    479343 Newbie
    Currently Being Moderated
    funtion returns only one value at a time. function parameters are always IN, no OUT is possible
  • 2. Re: can functions return multiple values?
    457512 Newbie
    Currently Being Moderated
    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
  • 3. Re: can functions return multiple values?
    Justin Cave Oracle ACE
    Currently Being Moderated
    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
  • 4. Re: can functions return multiple values?
    495617 Newbie
    Currently Being Moderated
    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;
  • 5. Re: can functions return multiple values?
    479343 Newbie
    Currently Being Moderated
    thank you Cave, is there any scenario why/where we use functions with OUT parameters.
  • 6. Re: can functions return multiple values?
    Justin Cave Oracle ACE
    Currently Being Moderated
    I can't think of a situation where it would be a good idea to have a function with OUT parameters, no.

    Justin
  • 7. Re: can functions return multiple values?
    479343 Newbie
    Currently Being Moderated
    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
  • 8. Re: can functions return multiple values?
    Justin Cave Oracle ACE
    Currently Being Moderated
    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
  • 9. Re: can functions return multiple values?
    Onenessboy Newbie
    Currently Being Moderated
    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>
  • 10. Re: can functions return multiple values?
    208428 Newbie
    Currently Being Moderated
    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;
  • 11. Re: can functions return multiple values?
    441005 Newbie
    Currently Being Moderated
    i want a clear example with syntax for function returning multiple values.
    how we have to write, we need a out parameter but in func. we can't write out parameter. or we have to write multiple return value. (we can wrie with pipe line but i don't need that.
    explin me.

    mail to my mail-id javvadsyed@rediffmail.com .
  • 12. Re: can functions return multiple values?
    316993 Pro
    Currently Being Moderated
    i want a clear example with syntax for function
    returning multiple values.
    how we have to write, we need a out parameter but in
    func. we can't write out parameter. or we have to
    write multiple return value. (we can wrie with pipe
    line but i don't need that.
    explin me.

    mail to my mail-id javvadsyed@rediffmail.com
    .
    SQL> SET SERVEROUTPUT ON
    SQL>CREATE OR REPLACE PACKAGE mypackage IS
    2 FUNCTION calc_sal (v1 OUT NUMBER,v2 NUMBER) RETURN NUMBER;
    3 PROCEDURE calc_bonus (v1 OUT NUMBER,v2 NUMBER);
    4 glob_var NUMBER;
    5 END;
    6 .
    SQL> /
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE mypackage IS
    2 FUNCTION calc_sal (v1 OUT NUMBER,v2 NUMBER) RETURN NUMBER;
    3 PROCEDURE calc_bonus (v1 OUT NUMBER,v2 NUMBER);
    4 globvar NUMBER;
    5 END;
    6 .
    SQL> /
    
    Package created.
    
    SQL>CREATE OR REPLACE PACKAGE BODY mypackage IS
    2 FUNCTION calc_sal (v1 OUT NUMBER,v2 NUMBER) RETURN NUMBER IS
    3 BEGIN
    4 v1:=100;
    5 globvar:=v1;
    6 RETURN 0;
    7 END;
    8 PROCEDURE calc_bonus (v1 OUT NUMBER,v2 NUMBER) IS
    9 BEGIN
    10 SELECT sal INTO v1 FROM emp WHERE empno=v2;
    11 END;
    12 END;
    13 .
    SQL> /
    
    Package body created.
    
    SQL>DECLARE
    2 a NUMBER:=0;
    3 b NUMBER:=7369;
    4 BEGIN
    5 mypackage.calc_bonus(a,b) ;
    6 DBMS_OUTPUT.PUT_LINE( b);
    7 END;
    8 .
    SQL> /
    7369
    
    PL/SQL procedure successfully completed.
    
    SQL> DECLARE
    2 a NUMBER:=1;
    3 b NUMBER:=7369;
    4 BEGIN
    5 a:=mypackage.calc_sal( a,7369);
    6 DBMS_OUTPUT.PUT_LINE( a);
    7 DBMS_OUTPUT.PUT_LINE( mypackage. globvar);
    8 END;
    9 .
    SQL> /
    0
    100
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    
    But you cant call function having OUT parameter from SQL e.g
    
    SQL> DECLARE
    2 a NUMBER:=1;
    3 b NUMBER:=7369;
    4 BEGIN
    5 SELECT mypackage.calc_sal(a,7369) INTO a FROM dual;
    6 DBMS_OUTPUT.PUT_LINE( a);
    7 DBMS_OUTPUT.PUT_LINE( mypackage. globvar);
    8 END;
    9 .
    SQL> /
    SELECT mypackage.calc_sal(a,7369) INTO a FROM dual;
    *
    ERROR at line 5:
    ORA-06550: line 5, column 10:
    PL/SQL: ORA-06572: Function CALC_SAL has out arguments
    ORA-06550: line 5, column 3:
    PL/SQL: SQL Statement ignored
    For returning more then one value you need procedure having OUT paramter mode.


    Moreover function cant return the images,text whereas stored procedures returns
    all by using OUT paramters cause they are pointers and used for pointing large
    value in a calling object rather reutrn the whole object.


    Khurram
  • 13. Re: can functions return multiple values?
    BluShadow Guru Moderator
    Currently Being Moderated
    For returning more then one value you need procedure
    having OUT paramter mode.
    Not necessarily true...
    SQL> create or replace type myretvals as object (valA number, valB number);
      2  /
    
    Type created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace function myfunc (myval number, divisor number) return myretvals is
      2    ret  myretvals := myretvals(0,0);
      3  begin
      4    ret.valA := trunc(myval/divisor);
      5    ret.valB := myval - (ret.valA*divisor);
      6    RETURN ret;
      7* end;
    SQL> /
    
    Function created.
    
    SQL> select myfunc(5,2) from dual;
    
    MYFUNC(5,2)(VALA, VALB)
    ---------------------------------------------------------------------------------------------------
    
    MYRETVALS(2, 1)
    
    SQL>
    Moreover function cant return the images,text whereas
    stored procedures returns
    all by using OUT paramters cause they are pointers
    and used for pointing large
    value in a calling object rather reutrn the whole
    object.
    Although SQL*Plus doesn't know how do display BLOB data (images) it is perfectly possible for functions to return such data. Text is the same. There's no need for OUT parameters and procudures to achieve this.
    SQL> create or replace function myfunc2 return blob is
      2    ret blob;
      3  begin
      4    select '12345' into ret from dual;
      5    return ret;
      6  end;
      7  /
    
    Function created.
    
    SQL> select myfunc2 from dual;
    SP2-0678: Column or attribute type can not be displayed by SQL*Plus
    SQL>
    SQL> declare
      2    myimg blob;
      3  begin
      4    myimg := myfunc2();
      5    dbms_output.put_line('I''ve got the image from the function!');
      6  end;
      7  /
    I've got the image from the function!
    
    PL/SQL procedure successfully completed.
    
    SQL>
  • 14. Re: can functions return multiple values?
    316993 Pro
    Currently Being Moderated
    BLOB variable stores a locator, which points to a large binary object through DBMS_LOB package.

    SQL> create or replace function myfunc2 return blob is
      2    ret blob;
      3  begin
      4    select '12345' into ret from dual;
      5    return ret;
      6  end;
      7  / 
     
    Function created.
     
    SQL> select myfunc2 from dual;
    SP2-0678: Column or attribute type can not be displayed by SQL*Plus
    SQL>
    SQL> declare
      2    myimg blob;
      3  begin
      4    myimg := myfunc2();
      5    dbms_output.put_line('I''ve got the image from the function!');
      6  end;
      7  / 
    I've got the image from the function!
     
    PL/SQL procedure successfully completed.
    Here you are just asigning one blob object to another BLOB type object rather returning.
    Although SQL*Plus doesn't know how do display BLOB data (images) it is
    perfectly possible for functions to return such data. Text is the same. There's no >need for OUT parameters and procudures to achieve this.
    Moreover function cant return the images,text whereas
    stored procedures returns
    all by using OUT paramters cause they are pointers
    and used for pointing large
    value in a calling object rather reutrn the whole
    object.
    If you want to return blob object you need to implement
    a procedure with OUT parameter having DBMS_LOB package
    for what i meant to say.Cause SQL has a limit of 4000
    bytes as well SQL cant display binary object.

    Function is totally failed to return continous lob
    object while procedure with OUT parameter mode as well
    DBMS_LOB.SUBSTR can do it.


    Correct me if i am wrong.

    Thanx for telling me returning more then one value by
    using object type its the exact and proeper way.

    Khurram
1 2 Previous Next