title: script for returning multiple values using function and arrays
body: <p>I need to create a script to return multiple values fetched using pl/sql </p>
  
  <p>script is:</p>
  
  <pre><code>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; --error 
  
    close test1;  
    end test4;  
    end;  
  /  
  </code></pre>
  
  <p>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:  </p>
  
  <pre><code>PLS-00371: at most one declaration for 'ARR1' is permitted              
  </code></pre>
  
  <p>I have even tried this    </p>
  
  <pre><code>create or replace package body dummy_m2  
   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 c1  
        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 ;    
  
    close test1;  
    end test4;  
    end;  
  </code></pre>
  
  <p>I have even tried this but returns the same error:   </p>
  
  <pre><code>PLS-00371: at most one declaration for 'C1' is permitted  
  </code></pre>