Oracle PL/SQL return array of rows

Question

Dear Oracle Developers,

I have searched and googled to find a solution for my problem but nothing helped me.

Situation :

TABLE : CUSTOMER(....);

My problem is : I want to create a stored procedure say get_customers to return an array of customer rows. I have no idea how to get this working.

I tried to create a type customer_rec and using a cursor to retrieve no more than maxRows

create or replace procedure get_customers(maxRows IN NUMBER, ??? OUT ????)

How to define the OUT parameter ?

How to retrieve the rows in the array using a cursor ?

Thanks a lot

Answer 1

I would like to answer this in a way that discourages passing around arrays, when passing around cursors is a more sound approach. It doesn't exactly answer the question as posed, but it is an answer. Thinking cursors instead of thinking arrays is more efficient and thus more scalable. Also, it can be much easier code to maintain.

create table customer (
   customer_id number(2) primary key,
   customer_name varchar2(200) );

insert into customer values (1, 'Customer One');
insert into customer values (2, 'Customer Two');
insert into customer values (3, 'Customer Three');
insert into customer values (4, 'Customer Four');
insert into customer values (5, 'Customer Five');
insert into customer values (6, 'Customer Six');
insert into customer values (7, 'Customer Seven');

CREATE OR REPLACE PACKAGE cursor_not_array IS

   FUNCTION get_customers(p_max_records INTEGER, p_id_start INTEGER, p_id_end INTEGER DEFAULT NULL) RETURN SYS_REFCURSOR;

END cursor_not_array;

CREATE OR REPLACE PACKAGE BODY cursor_not_array IS

   c_max_customer_id CONSTANT NUMBER(2) := 99;

   FUNCTION get_customers(p_max_records INTEGER, p_id_start INTEGER, p_id_end INTEGER DEFAULT NULL) RETURN SYS_REFCURSOR IS
      v_result SYS_REFCURSOR;
   BEGIN
      OPEN v_result FOR
         SELECT customer_id,
                customer_name
           FROM customer
          WHERE customer_id BETWEEN p_id_start AND nvl(p_id_end, c_max_customer_id)
          ORDER BY customer_id;

      RETURN v_result;
   END;

END cursor_not_array;

Answer 2

You could create a package like this:

create or replace package customers is

  type customers_array is table of customer%rowtype index by binary_integer;

  procedure get_customers(maxRows IN NUMBER, customer_array OUT customers_array);

end customers;

create or replace package body customers is

  procedure get_customers(maxRows IN NUMBER, customer_array OUT customers_array) is
    cursor c_customers is
      select * 
      from customers;
      where rownum <= maxRows;

    i number := 1;
  begin
    for r in c_customers loop
      customer_array(i) := r;
      i := i + 1;
    end loop;
  end get_customers;

end customers;

And then call the get_customers procedure from wherever you want to...

Answer 3

first time create VARRAY type.

'create TYPE CUSTARRAY is VARRAY(100) OF VARCHAR2(30);'

varray limit is depends on you.

then create procedure that return CUSTARRAY type parameter.

`create 
procedure prc_get_arr(p_maxrow in number, p_customers out custarray)
as
my_cust custarray := custarray(); 
cursor c_cust is select name from CUSTOMER where rownum<p_maxrow; 
v_customer varchar2(64);
begin
open c_cust;
loop
 fetch c_cust into v_customer;
  exit when c_cust%notfound;
    my_cust.extend; 
    my_cust(my_cust.count) := v_customer; 
 end loop;
 close c_cust;
 p_customers:=my_cust;

end;`

Now call this procedure

 DECLARE
P_MAXROW NUMBER;
p_customers custarray;
v_cnt number:=0;
 begin
P_MAXROW := 22;
prc_get_arr( p_maxrow => p_maxrow, p_customers => p_customers );
v_cnt:=p_customers.count;
for i in p_customers.first..p_customers.last loop

dbms_output.put_line('P_CUSTOMERS = ' || p_customers(i));
end loop;

end;

Answer 4

You can, using the SYS_REFCURSOR on your function output.

Answer 5

First you have to define a collection :

TYPE customers_array IS TABLE OF customer%ROWTYPE
    INDEX BY BINARY_INTEGER;

Then your procedure simply have to fetch the result into that collection. You're procedure could be written as follow:

CREATE OR REPLACE PACKAGE your_pkg
AS

    TYPE customers_array IS TABLE OF customer%ROWTYPE
        INDEX BY BINARY_INTEGER;

    PROCEDURE get_customers(pn_max_rows    IN NUMBER,
                            pt_coustomers OUT customers_array);
END your_pkg;

CREATE OR REPLACE PACKAGE BODY your_pkg
AS

    PROCEDURE get_customers(pn_max_rows    IN NUMBER,
                            pt_coustomers OUT customers_array)
    IS
    BEGIN  
        SELECT *
          BULK COLLECT INTO pt_coustomers
          FROM customers
         WHERE rownum <= pn_max_rows;
    END get_customers;

END your_pkg;