A lot of Oracle result sets in Odp.net, no refcursors

Question

SQL Server can return the results of multiple queries in a single trip, for example:

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

Oracle does not like this syntax. You can use reference cursors, for example:

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

However, you are liable for opening / closing cursors, and you can keep database locks for a long period. What I would like to do is get the results for these two queries in one shot using Odp.net. Is it possible?

Answer 1

In Oracle, the orientation cursor is a pointer to the data, not the data itself. Thus, if the procedure returns two reference cursors, the client will still have to go and extract lines from these cursors (and carry network bumps).

Thus, if the data volumes are small, you probably want to call a procedure that simply returns values. If the data volumes are large (thousands of lines), then this will not be a single network trip in any case, so one or two when switching between cursors will not matter much.

Another choice is to have one choice, return all rows. It could be simple UNION ALL

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

It can be a pipeline function of a table.

 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 that the latest versions of ODP for 11g allow you to determine the types of users that can help.