Batch multiple select statements when calling Oracle from ADO.NET

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

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

How about:

var sql = @"            select * from table1 UNION            select * from table2 UNION            select * from table3";