使用Oracle的多个结果集 [英] Multiple Result Sets with Oracle

问题描述

一个简单的问题:

我的代码如下:

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

现在，我希望在DataSet中获得两个表，但我宁愿得到一个异常，告诉我SQL语法不正确...是不是那样认识的..?有任何想法吗?

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?

编辑#1:也无法添加 BEGIN + END; (多种版本)

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

编辑#2:使用立即执行包装选择将运行，但不会返回结果集.

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.

推荐答案

您应该编写一个返回ref cursors的匿名pl/sql块.

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

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

这篇关于使用Oracle的多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！