Batch multiple select statements when calling Oracle from ADO.NET
ado.net, c#, oracle, resultset
Solution
You should write an anonymous pl/sql block that returns 3 ref cursors.
edit1: Here it is done in an anonymous pl/sql block with one cursor. It should work with three too. Oracle ref cursors don't lock data and they are the fastest way to return a result set from a pl/sql procedure or an anonymous pl/sql bloc.
http://www.oracle.com/technetwork/issue-archive/2006/06-jan/o16odpnet-087852.html
Problem
I want to batch multiple select statements to reduce round trips to the database. The code looks something like the pseudo code below. It works perfectly on SQL Server, but does not work on Oracle - Oracle complains about the sql syntax. I have had a look around and the only examples I can find of returning multiple result sets from Oracle are using Stored Procedures. Is it possible to do this in Oracle without using Stored Procedures? I am using the MS Oracle data provider, but could use the ODP.Net one if needed. ``` var sql = @" select * from table1 select * from table2 select * from table3"; DbCommand cmd = GetCommand(sql); using(var reader = cmd.ExecuteReader()) { dt1.Load(reader); reader.NextResult(); dt2.Load(reader); reader.NextResult(); dt3.Load(reader); } ```