1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL/SQL function to return more than one value

Discussion in 'SQL PL/SQL' started by Julian, Dec 10, 2008.

  1. Julian

    Julian Forum Advisor

    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    90
    hello, is it possible to return more than one value from a PL/SQL function. for example, in my function I have a IF block from where I want to return two values:
    Code (Text):

    ......
    IF sales_cur%NOTFOUND
    THEN
       CLOSE sales_cur;
       RETURN NULL;
    ELSE
       CLOSE sales_cur;
       RETURN [B]return_value1, return_value2[/B];
    END IF;
     
    I know the above code will not work, but just to demonstrate, is it possible to return two values with a function?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Its big NO . Function can return only One value at a time whether it is VARCHAR2, NUMBER ,DATE , PL/SQL table or refcursor .

    Anyway , in your case , it is possible by any of the following methods.

    1. Concatenate return_value1 and return_value2 and return as a string (delimited by some special character) and process the string later.

    2. Convert the function to procedure and return result through OUT parameters .(though it is possible to do use OUT paramete in function)

    3. Pass Ref cursor or table type instance as return type

    4. use Table functions .
     
  3. Wivani

    Wivani Guest

    Or you could wrap the values into a collection or even an object type.
     
  4. iamsonal

    iamsonal Forum Advisor

    Messages:
    8
    Likes Received:
    2
    Trophy Points:
    250
    Try this with the following.

    Code (Text):
    create or replace function my_func(myno number, myname out
    varchar2) return number as
      mysal emp.sal%type;
    begin
      select ename, sal into myname, mysal from emp where
    empno=myno;
      return sal;
    end;
    In the above example we are returning first value mysal using return statement and second value myname using out parameter.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Collction concept will work out Dude. Try it now.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Answering to 3 Year old question ? Even the OP might have forgotten his question :)

    Anyway Collection could be an option. Table function works on collections.
     
  7. pola

    pola Guest

    could you please let me know how to execute the above mentioned function
     
  8. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    What is exactly your requirement?
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And I know it's three years old but the example doesn't work:

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION my_func(myno NUMBER, myname OUT
      2  varchar2) RETURN NUMBER AS
      3    mysal emp.sal%TYPE;
      4  BEGIN
      5    SELECT ename, sal INTO myname, mysal FROM emp WHERE
      6  empno=myno;
      7    RETURN sal;
      8  END;
      9  /
     
    Warning: FUNCTION created WITH compilation errors.
     
    SQL>
    SQL> SHOW errors
    Errors FOR FUNCTION MY_FUNC:
     
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/3      PL/SQL: Statement ignored
    7/10     PLS-00201: identifier 'SAL' must be declared

    SQL>
    SQL> CREATE OR REPLACE FUNCTION my_func(myno NUMBER, myname OUT
      2  varchar2) RETURN NUMBER AS
      3    mysal emp.sal%TYPE;
      4  BEGIN
      5    SELECT ename, sal INTO myname, mysal FROM emp WHERE
      6  empno=myno;
      7    RETURN mysal;
      8  END;
      9  /
     
    FUNCTION created.
     
    SQL>
    SQL> variable mynm varchar2(20)
    SQL>
    SQL> SELECT my_func(7734, :mynm) FROM dual;
    SELECT my_func(7734, :mynm) FROM dual
           *
    ERROR at line 1:
    ORA-06572: FUNCTION MY_FUNC has OUT arguments

    SQL>
    One would think that people would test such code before putting into the public eye.