Multiple resultsets from Oracle in Odp.net,without refcursors

Question

SQL Server is able to return the results of multiple queries in a single round-trip, e.g:

select a, b, c from y;
select d, e, f from z;

Oracle doesn't like this syntax. It is possible to use reference cursors, like this:

begin 
  open :1 for select count(*) from a; 
  open :2 for select count(*) from b; 
end; 

However, you incur a penalty in opening/closing cursors and you can hold database locks for an extended period. What I'd like to do is retrieve the results for these two queries in one shot, using Odp.net. Is it possible?

Answer 1

In Oracle, reference cursor is a pointer to data, rather than data itself. So if a procedure returns two reference cursors, the the client still has to go and fetch the rows from those cursors (and incur the network hits).

As such, if the data volumes are small, you probably want to call a procedure that just returns the values. If the data volumes are large (thousands of rows) then it won't be a single network trip anyway, so an extra one or two as you switch between cursors isn't going to make much difference.

Another choice is have a single select return all the rows. That might be a simple UNION ALL

select a, b, c from y union all select d, e, f from z;

It could be a pipelined table function

create or replace package test_pkg is
    type rec_two_cols is record
        (col_a  varchar2(100),
        col_b       varchar2(100));
    type tab_two_cols is table of rec_two_cols;
    function ret_two_cols return tab_two_cols pipelined;
end;
/

create or replace package body test_pkg is
    function ret_two_cols return tab_two_cols pipelined
    is
        cursor c_1 is select 'type 1' col_a, object_name col_b from user_objects;
        cursor c_2 is select 'type 2' col_a, object_name col_b from user_objects;
        r_two_cols  rec_two_cols;
    begin
        for c_rec in c_1 loop
            r_two_cols.col_a := c_rec.col_a;
            r_two_cols.col_b := c_rec.col_b;
            pipe row (r_two_cols);
        end loop;
        for c_rec in c_2 loop
            r_two_cols.col_a := c_rec.col_a;
            r_two_cols.col_b := c_rec.col_b;
            pipe row (r_two_cols);
        end loop;
        return;
    end;
end;
/
select * from table(test_pkg.ret_two_cols);

I believe the most recent versions of ODP for 11g allow user-defined types which may help.