Multiple resultsets from Oracle in Odp.net,without refcursors
- by James L
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?
