1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. 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!

return multiple values through function using array

Discussion in 'SQL PL/SQL' started by meghna, Apr 8, 2011.

  1. meghna

    meghna Guest

    Hi,

    I need to create a script to return multiple values fetched using pl/sql script is:
    create or replace package body dummy_m
    is
    type arr1 is table of temp_particulars.event_start_date%type;
    var temp_particulars.event_start_date%type;

    type c1 is ref cursor
    return temp_date%rowtype;

    function TEST4(
    infranchise IN temp_particulars.franchise%TYPE,
    inoperator IN temp_particulars.billing_operator%TYPE,
    inbillingperiod IN temp_particulars.billing_period%TYPE,
    intrafficperiod IN temp_particulars.traffic_period_name%TYPE,
    incost IN temp_particulars.unit_cost_used%TYPE
    )
    return arr1%rowtype--error
    is
    test1 c1;
    my_arr arr1;
    cnt number;
    begin
    --my_arr :=arr1();
    cnt:=0;
    delete from temp_date;
    insert into temp_date SELECT distinct t.event_start_date
    FROM temp_particulars t
    WHERE t.billing_operator = inoperator
    AND t.franchise = infranchise
    AND t.billing_period= inbillingperiod
    AND t.traffic_period_name= intrafficperiod
    and t.unit_cost_used=incost;

    open test1 for select * from temp_date ;

    fetch test1 bulk collect into my_arr;

    loop

    fetch test1 bulk collect into my_arr;
    cnt:=cnt+1;

    dbms_output.put_line(cnt);

    exit when test1%notfound;
    end loop;
    return my_arr;

    close test1;
    end test4;
    end;
    /

    here I have to return the value of event_start_date where multiple values are returned but its showing multiple errors like the first error is:
    PLS-00371: at most one declaration for 'ARR1' is permitted
     
  2. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    This is the complete code ?
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    There are multiple ways of doing it ..

    1. By using just Table function.


    Code (SQL):
    SQL> CREATE TYPE emp_name_o AS OBJECT
    (
    ename  VARCHAR2(10)
    )  2    3    4  ;
      5  /

    TYPE created.

    SQL> CREATE TYPE emp_name_t AS TABLE OF emp_name_o
      2  /

    TYPE created.

    SQL> CREATE OR REPLACE FUNCTION GET_EMP_LIST1
    ( ip_deptno_n   NUMBER
      2    3  )
      4  RETURN EMP_NAME_T
      5  AS
      6      V_ENAME EMP_NAME_T;
      7  BEGIN
      8
      9      SELECT EMP_NAME_O(ENAME)
     10      BULK COLLECT INTO V_ENAME
     11      FROM EMP
     12      WHERE DEPTNO = ip_deptno_n ;
     13
     14      RETURN V_ENAME;
     15  END;
     16    /

    FUNCTION created.

    SQL> SELECT * FROM TABLE(GET_EMP_LIST1(10));

    ENAME
    ----------
    CLARK
    KING
    MILLER

    SQL>
    2. By using just Table function and CAST(MULTISET ...).

    Code (SQL):
    SQL> CREATE TYPE emp_name_o AS OBJECT
    (
    ename  VARCHAR2(10)
    )  2    3    4  ;
      5  /

    TYPE created.

    SQL> CREATE TYPE emp_name_t AS TABLE OF emp_name_o
      2  /

    TYPE created.

    SQL> CREATE OR REPLACE FUNCTION GET_EMP_LIST2
    ( ip_deptno_n   NUMBER
      2    3  )
      4  RETURN EMP_NAME_T
      5  AS
      6      V_ENAME EMP_NAME_T;
      7  BEGIN
      8
      9      SELECT
     10      CAST ( MULTISET ( SELECT ENAME
     11                 FROM EMP
     12                 WHERE DEPTNO = ip_deptno_n ) AS EMP_NAME_T )
     13      INTO V_ENAME
     14      FROM DUAL;
     15
     16      RETURN V_ENAME;
     17  END;
     18
     19  /

    FUNCTION created.

    SQL> SELECT * FROM TABLE(GET_EMP_LIST2(10));

    ENAME
    ----------
    CLARK
    KING
    MILLER

    SQL>
    Hope this helps