Bulk collect into ref cursor

[IT4EVR]

Is it possible to bulk collect into a REF CURSOR and pass that REF CURSOR out to the client?

I was under the impression you couldn't but wanted to be sure.

 

[SantaMufasa]

IT4EVR,

Please articulate your objective..."BULK COLLECT" is for loading a PL/SQL memory array..."REF CURSOR" is for staging the rows of a query for row-by-row processing.

1) You can BULK COLLECT into an array, which you can then pass as an OUT variable to a calling procedure;

2) You can open a REF CURSOR, which you can also pass as an OUT variable to a calling procedure.

Which do you prefer?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[IT4EVR]

My goal is to use the most efficient means of returning records to the client application. The method I use now is to fill a ref cursor and pass it out to the client, which then processes it. But I've become aware that BULK COLLECT is a more efficient means.

If I could bulk collect into a varray and then process it on the client, that would be fine.

I'm not sure if you can do that with .NET but I know I can process the CURSOR in .NET.

 

[SantaMufasa]

Okay, then here is code that illustrates how to BULK COLLECT into an array (in the called procedure), then pass back the array to the calling routine:

create or replace package IT4EVR is
    type dept_table is table of s_dept%rowtype;
    procedure get_recs (results out dept_table);
end;
/

Package created.

create or replace package body IT4EVR is
  procedure get_recs (results out dept_table) is
  begin
    select * bulk collect into results
      from s_dept
     order by id;
  end;
end;
/

Package body created.

set serveroutput on format wrap
Declare
    my_depts it4evr.dept_table;
begin
    it4evr.get_recs (my_depts);
    for i in 1..my_depts.count loop
        dbms_output.put_line(my_depts(i).id||': '
                           ||my_depts(i).name);
    end loop;
end;
/
10: Finance
31: Sales
32: Sales
33: Sales
34: Sales
35: Sales
41: Operations
42: Operations
43: Operations
44: Operations
45: Operations
50: Administration

PL/SQL procedure successfully completed.

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]