CODEWITHSUNDEEP
c#oracle-databaseado.net
Jaster
Jaster

Reputation: 8581

Multiple Result Sets with Oracle

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.

Upvotes: 6

Views: 3313

Answers (1)

Hasan Fathi
Hasan Fathi

Reputation: 6086

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

Upvotes: 3

Related Questions