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!

Can we return multiple values through function in oracle?

Discussion in 'SQL PL/SQL' started by jagadekara, Nov 8, 2013.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi all,

    Can we return multiple values through function in oracle?
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Yes, we can...

    1. Using out parameters..
    2. Collections and Records...

    However 1 is not advisable ...
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Kiran,
    can we call it from Sql ?
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Yes Jagadekara ...
    1. with using bind variables for out parameters type.
    2. For record type bind variables are not required.
     
  5. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    If a PL/SQL function is called from SQL engine, then only SQL datatypes can be used as the type of the returned value. As a result, a record data type cannot be used as a returned type if the function is to be called from SQL engine level. An alternative would be schema level object types along with schema level nested table types defined based on those object types.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Actually 1 is not available:

    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 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>
    This is why one should test code and suggestions before posting them to the group.
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    1)

    Code (SQL):


    CREATE OR REPLACE FUNCTION example_div  (p_dividend  INT,p_divider INT , p_out OUT INT )
    RETURN varchar2
    AS
           RESULT varchar2(30) := 'No error';
    BEGIN
      BEGIN    
        p_out := trunc(p_dividend/p_divider);
      exception
      WHEN zero_divide THEN RESULT := 'Bad divider';
      END;
        RETURN RESULT;    

    END;
    /


    rem sample CALL.....

    DECLARE
        v_dividend INT := 10;
        v_divider  INT := 3;
        res INT;
        res_msg varchar2(255);
    BEGIN
        res_msg := example_div(v_dividend,v_divider,res);
        dbms_output.put_line ('f_Div('||v_dividend||','||v_divider||') = '||res );
        dbms_output.put_line ('Error = '||res_msg);
    END;

     






    2) using object table(collection) and table function

    Code (SQL):


    CREATE OR REPLACE TYPE t_4test IS TABLE OF NUMBER
    /
    CREATE OR REPLACE FUNCTION cr_list_number (p_max INT)
        RETURN t_4test
        AS
        func_res t_4test;
        BEGIN
             SELECT
               dbms_random.VALUE
             bulk collect INTO  func_res        
             FROM dual
             CONNECT BY level <= p_max;        
        RETURN func_res;      
        END ;  
    /  

    SET pagesize 0
    SELECT * FROM TABLE(cr_list_number(30));


     

    3) using pl/sql record


    Code (SQL):

    SET serveroutput ON
    DECLARE
        TYPE t_rec IS record
        ( x INT,
          y INT,
          z INT
        );  
        v_rec t_rec;
        FUNCTION set_record
            (p_x INT,p_y INT,p_z INT)
        RETURN t_rec  
        AS
            res t_rec;
        BEGIN
            res.x := p_x;
            res.y := p_y;
            res.z := p_z;      
        RETURN res;
        END;              
    BEGIN
       v_rec := set_record(10,11,-2);
       dbms_output.put_line('X='||v_rec.x||' '||' Y='||v_rec.y||' Z='||v_rec.z);
    END;      
    /
     


    4)
    Code (SQL):

    SET serveroutput ON
    CREATE OR REPLACE package pkg_test
    IS
        TYPE t_rec IS record
        ( x INT DEFAULT 10,
          y INT DEFAULT 1,
          z INT DEFAULT 3
        );

        TYPE t_tab IS TABLE OF t_rec;
       
        FUNCTION get_val  RETURN t_tab pipelined;
    END;
    /
    CREATE OR REPLACE package body pkg_test
    IS
     FUNCTION get_val RETURN t_tab pipelined
     AS
        rec t_rec;
     BEGIN
         rec.x := 10;
         rec.y := 0;
         rec.z := 11;
         pipe ROW(rec);
          RETURN;
     END;
    END;
    /

    SELECT * FROM TABLE(pkg_test.get_val);

     
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I hope all of this code has been tested; if so why did you not include the generated output?
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Yes, of course, the code is tested...
    I can give an output of results if it is necessary...
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It should be necessary; there are those in this forum who have little to no experience with such code and take any examples offered as working. When they don't (from untested code) they can believe it's their fault, not the error of the poster, causing the issue.

    A thorough answer requires that output from the offered code be posted.
     
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Yes, I here will agree with your judgement.
     
  12. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam