1 2 Previous Next 19 Replies Latest reply: Mar 22, 2011 9:38 PM by 844722 RSS

    can functions return multiple values?

    495617
      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
          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
            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?
              JustinCave
              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
                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
                  thank you Cave, is there any scenario why/where we use functions with OUT parameters.
                  • 6. Re: can functions return multiple values?
                    JustinCave
                    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
                      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?
                        JustinCave
                        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
                          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
                            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
                              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?
                                orawarebyte
                                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
                                  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?
                                    orawarebyte
                                    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