Batch multiple select statements when calling Oracle from ADO.NET

Question

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);
}

Solution 1

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

Solution 2

An example in C# with multiple cursors and an input parameter:

string ConnectionString = "connectionString";
OracleConnection conn = new OracleConnection(ConnectionString);
StringBuilder sql = new StringBuilder();

sql.Append("begin ");
sql.Append("open :1 for select * from table_1 where id = :id; ");
sql.Append("open :2 for select * from table_2; ");
sql.Append("open :3 for select * from table_3; ");
sql.Append("end;");

OracleCommand comm = new OracleCommand(sql.ToString(),_conn);

comm.Parameters.Add("p_cursor_1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

comm.Parameters.Add("p_id", OracleDbType.Int32, Id, ParameterDirection.Input);

comm.Parameters.Add("p_cursor_2", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

comm.Parameters.Add("p_cursor_3", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

conn.Open();

OracleDataReader dr = comm.ExecuteReader();

Comments

[stuart donald]

Thanks for your response. I tried putting semi-colons between the statements but had the same problem. I dont want to use stored procs as the sql will be dynamically generated, so will have a variable number of result sets returned. The provided sample is a simplified version of what I am doing.

[Alberto Martinez]

The link is now dead but here is a archived copy of the Internet Archive: web.archive.org/web/20060412173402/http://www.oracle.com/…

[vapcguy]

All of the columns would have to be the same, but could work if they are.

[sam]

kindly can you help me with this stackoverflow.com/questions/41306344/…

[Charles Byrne]

Link appears to be up circa 2017, but I do love the Wayback Machine. It has saved my neck on a few occasions.

[Charles Byrne]

Daniel's Example as well as the ODP.Net link example show the parameters being added in the same order as they are seen in SQL statement. If they are not in the same order you will get an error stating the wrong number or types of arguments. In most situations this isn't an issue, but in instances where you need to add the parameters out of sequence then set the OracleCommand BindByName = true;