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.

Answer 1

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