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!

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