Multiple Result Sets with Oracle

Question

Simple Question:

My code looks like this:

        var con = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.10.8)(PORT=1521))(CONNECT_DATA=(SID=orcl12c)));");
        con.Open();

        var adp = new OracleDataAdapter("select * from adr;select * from person;", con);
        var ds = new DataSet();
        adp.Fill(ds);

Now I would expect to get two tables in the DataSet, but I rather get an exception telling me that the SQL Syntax is not correct... It seems the ; is not recognized that way..? Any Ideas?

Edit #1: Also Adding BEGIN+END; does not work (multiple variations)

Edit #2: Wrapping the selects with execute immediate will run, but won't return a result set.

Solution: Combine the provided answer with Using Dapper with Oracle stored procedures which return cursors and enjoy.

Possible duplicate of [Execute multiple queries in single Oracle command in C#](https://stackoverflow.com/questions/31917301/execute-multiple-queries-in-single-oracle-command-in-c-sharp) — gunr2171, Jul 09 '18 at 15:46
Run one query at a time and omit the semicolon, or put both queries in a PL/SQL block https://stackoverflow.com/questions/12262145/ora-00911-invalid-character — David Faber, Jul 09 '18 at 15:49
Not a duplicate and putting it in one block does not resolve the question, but digs for another solution. — Jaster, Jul 17 '18 at 09:48

Hasan Fathi · Accepted Answer · 2018-07-17T07:53:58.093

3

You should write an anonymous pl/sql block that returns ref cursors.

Try this in ADO.NET:

    oraConnection = new OracleConnection();
    da = new OracleDataAdapter();
    ds = new DataSet();

    oraConnection.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.10.8)(PORT=1521))(CONNECT_DATA=(SID=orcl12c)));";
    cmdText = "begin open :1 for select * from adr; open :2 for select * from person; end;"; 
    cmd = new OracleCommand();
    cmd.CommandText = cmdText;
    cmd.Connection = oraConnection;
    cmd.CommandType = CommandType.Text; 

    OracleParameter refcur1 = cmd.Parameters.Add("Refcur", OracleDbType.RefCursor);
    refcur1.Direction = ParameterDirection.Output;
    OracleParameter refcur2 = cmd.Parameters.Add("Refcur", OracleDbType.RefCursor);
    refcur2.Direction = ParameterDirection.Output;

        da.SelectCommand = cmd;
        da.Fill(ds);

edited Jul 17 '18 at 07:53

answered Jul 15 '18 at 13:41

Hasan Fathi

5,610
4
42
60

1

As said, it is oracle related. @HasanFathi your answer works, but I was hoping for a solution with pure SQL so I wouldn't need to handle parameters. Why? Because I would like to use Dapper, which works with pure string statements and connections. – Jaster Jul 17 '18 at 07:29
@Jaster my solution for `ADO.NET`, in `Dapper.NET` you can use `QueryMultiple`. – Hasan Fathi Jul 17 '18 at 07:44
@Jaster in `ADO.NEt` you should use my suggested approach – Hasan Fathi Jul 17 '18 at 07:47
@Jaster you wanted functionality not handled through pure sql , you want a solution for get multiple result that return from sql that related to dapper or ado – Hasan Fathi Jul 17 '18 at 07:50
No worries, I'll accept the answer... QueryMultiple will not work as it only accepts a string, not a command... – Jaster Jul 17 '18 at 08:24

Multiple Result Sets with Oracle

1 Answers1

Linked