script for returning multiple values using function and arrays

Question

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; --error 

  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

I have even tried this

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;

I have even tried this but returns the same error:

PLS-00371: at most one declaration for 'C1' is permitted

Dave Costa · Accepted Answer · 2011-04-08 13:12:30Z

Is the arr1 type already declared in the package header? It should be, if you intend to call this function from code external to the package; and that would explain why you are getting the error about multiple declarations. Verify that the type is already declared in the header, and remove the declaration from the package body.

More generally, your code looks like a lot of mucking about with cursors for no good reason. All it appears you are trying to do is populate an array with the results of a query. Just do your select directly into the array.

create or replace package body dummy_m  
 is 
  -- This should probably be declared in the package header
  --type arr1 is table of temp_particulars.event_start_date%type;  

    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
      is
        my_arr arr1 := arr1();
  begin
      SELECT distinct t.event_start_date
      BULK COLLECT INTO my_arr
        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;

      return my_arr;

  end test4;  
  end;  
/

thanx.. removing declaration from pkg body in my code n this code.. both worked.

Tony Andrews · Accepted Answer · 2011-04-08 11:03:45Z

0

The return clause should be:

return arr1

not:

return arr1%rowtype--error

Here is some equivalent code that runs without error:

declare
  type arr1 is table of user_tables.last_analyzed%type;  
  var user_tables.last_analyzed%type;  
  type  c1  is ref cursor
      return user_tables%rowtype;  
  function TEST4 (p number)
      return arr1
  is
    test1 c1;
    my_arr arr1;
    cnt number; 
  begin
   null;
  end TEST4;
begin
  null;
end;

edited Apr 8, 2011 at 11:03

answered Apr 8, 2011 at 9:44

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

3 Comments

Tony Andrews Over a year ago

This looks suspect: "type c1 is ref cursor return temp_date%rowtype;". Is temp_date a table?

meghna Over a year ago

Yes, temp_date is a table having just 1 column where I am inserting the required values as u can see in the code

meghna Over a year ago

the above code runs as nothing is being returned.. when u try to use the array n return the same it throws error and even if it doesnt, returns only the last value fetched

Collectives™ on Stack Overflow

script for returning multiple values using function and arrays

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related