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

http://stackoverflow.com/questions/308963/how-to-split-oracle-sql-statements-for-ado-net — Kirtan, Jun 30 '09 at 09:23
Thanks. I had a look at that one, but it is a slightly different scenario, as the SQL statements are not returning any result sets. — stuart donald, Jun 30 '09 at 09:33

score 11 · Answer 1 · answered Jun 18 '10 at 15:34

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

kindly can you help me with this http://stackoverflow.com/questions/41306344/how-to-execute-multiple-oracle-query-c-sharp/41308024?noredirect=1#comment69821736_41308024 — sam, Dec 24 '16 at 00:52
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; — Charles Byrne, Jan 18 '17 at 16:37

score 6 · Accepted Answer · edited Apr 18 '13 at 14:00

6

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

edited Apr 18 '13 at 14:00

Fulvio

1,615
1
16
18

answered Jun 30 '09 at 16:30

tuinstoel

7,248
27
27

1

The link is now dead but here is a archived copy of the Internet Archive: http://web.archive.org/web/20060412173402/http://www.oracle.com/technology/oramag/oracle/06-jan/o16odpnet.html – Alberto Martinez May 23 '11 at 18:39
Link appears to be up circa 2017, but I do love the [Wayback Machine](http://web.archive.org). It has saved my neck on a few occasions. – Charles Byrne Jan 13 '17 at 13:33

score 0 · Answer 3 · edited May 23 '17 at 12:01

0

why not use stored procedures instead?

But, if you want to batch them in an inline query, you can use a semicolon (;) to seperate the statements.

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

EDIT: You take a look at this SO question.

EDIT2: Take a look at this answer.

edited May 23 '17 at 12:01

Community

1
1

answered Jun 30 '09 at 09:05

Kirtan

21,295
6
46
61

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. – stuart donald Jun 30 '09 at 09:14

score 0 · Answer 4 · answered Jun 30 '09 at 17:31

0

How about:

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

answered Jun 30 '09 at 17:31

DCookie

42,630
11
83
92

All of the columns would have to be the same, but could work if they are. – vapcguy Nov 17 '16 at 18:14

Batch multiple select statements when calling Oracle from ADO.NET

4 Answers4

Linked