return multiple values through function using array

in Oracle Database; Hi, I need to create a script to return multiple values fetched using pl/ sql script is: create or replace ...
+ Post Reply + Post New Topic
Results 1 to 3 of 3
  1. #1
    meghna is offline Junior Member
    Points: 40, Level: 1
    Join Date
    08 Apr 2011
    Posts
    1
    Points
    40

    return multiple values through function using array

    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. #2
    debasisdas's Avatar
    debasisdas is offline Member
    Points: 530, Level: 10
    Join Date
    15 Nov 2008
    Location
    Bangalore, India
    Posts
    46
    Points
    530

    Re: return multiple values through function using array

    This is the complete code ?
    Share Your Knowledge.

  3. #3
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: return multiple values through function using array

    Quote Originally Posted by meghna View Post
    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

    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

    Raj.

Other Solutions
  1. PL/SQL function to return more than one value
    By oracle_new in forum SQL PL/SQL
    Replies: 8
    Last Post: 09-03-2013, 06:18 AM
  2. ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent
    By Aj_aaron2002@yahoo.co.uk in forum Oracle Application Express (APEX)
    Replies: 1
    Last Post: 12-17-2012, 08:31 PM
  3. Replies: 5
    Last Post: 01-25-2011, 12:28 PM
  4. Replies: 1
    Last Post: 05-05-2010, 06:34 AM
  5. Replies: 2
    Last Post: 09-15-2009, 12:27 PM