TLDR：你错过了游标绑定变量上的冒号：

 OPEN :rcursor FOR SELECT * FROM CSM_RECORDS;-- WHERE OBJECT_UID IN (t_Ids); 

完整答案：您可能知道，在oracle中有pl / sql上下文（您的匿名块）和SQLplus上下文。 这是一个带有SQLplus变量的块，带有相关绑定变量的块，以及最后的SQLplus打印：

 var rcursor refcursor var fromDate varchar2(50) var toDate varchar2(50) exec :fromDate := '1-mar-2014'; exec :toDate := '1-apr-2014'; begin open :rcursor for SELECT trunc(to_date(:fromDate,'dd-mon-yyyy')) + NUMTODSINTERVAL(n,'day') AS Full_Date FROM ( select (level-1) n from dual connect by level-1 <= trunc(to_date(:toDate,'dd-mon-yyyy')) - trunc(to_date(:fromDate,'dd-mon-yyyy')) ) ; end; / print rcursor 

在.net中执行块时，ODP.net正在处理在SQLplus级别完成的准备工作。 这是从.net执行的相同块（作为nunit测试）：

 [Test] public void RefCursorFromBatch() { OracleCommand cmd = new OracleCommand(); cmd.CommandText = @" begin open :rcursor for SELECT trunc(to_date(:fromDate,'dd-mon-yyyy')) + NUMTODSINTERVAL(n,'day') AS Full_Date FROM ( select (level-1) n from dual connect by level-1 <= trunc(to_date(:toDate,'dd-mon-yyyy')) - trunc(to_date(:fromDate,'dd-mon-yyyy')) ) ; end;"; cmd.BindByName = true; cmd.Parameters.Add("fromDate", OracleDbType.Date).Value = DateTime.Today.AddDays(-30); cmd.Parameters.Add("toDate", OracleDbType.Date).Value = DateTime.Today; cmd.Parameters.Add("rcursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output; using (cmd.Connection = new OracleConnection("...")) { cmd.Connection.Open(); var reader = cmd.ExecuteReader(); OracleDataAdapter da = new OracleDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); Assert.Greater(dt.Rows.Count, 0); } } 

你可以在这里阅读更多内容： http ： //www.brothersincode.com/post/executing-SQL-Plus-Batches-from-Net.aspx

如果您还没有答案，这里有一些我已确认正在运行的示例代码，您可以将其用作起点。

 using System; using System.Data; using Oracle.ManagedDataAccess.Client; using Oracle.ManagedDataAccess.Types; namespace ConsoleApplication1 { class Class1 { [STAThread] static void Main(string[] args) { try { string conString = "User Id=scott;Password=tiger;Data Source=orcl;Pooling=false;"; OracleConnection con = new OracleConnection(); con.ConnectionString = conString; con.Open(); string cmdtxt = "BEGIN " + "OPEN :1 for select ename, deptno from emp where deptno = 10; " + "OPEN :2 for select ename, deptno from emp where deptno = 20; " + "OPEN :3 for select ename, deptno from emp where deptno = 30; " + "END;"; OracleCommand cmd = con.CreateCommand(); cmd.CommandText = cmdtxt; OracleParameter p1 = cmd.Parameters.Add("refcursor1", OracleDbType.RefCursor); p1.Direction = ParameterDirection.Output; OracleParameter p2 = cmd.Parameters.Add("refcursor2", OracleDbType.RefCursor); p2.Direction = ParameterDirection.Output; OracleParameter p3 = cmd.Parameters.Add("refcursor3", OracleDbType.RefCursor); p3.Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); OracleDataReader dr1 = ((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader(); OracleDataReader dr2 = ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader(); while (dr1.Read() && dr2.Read()) { Console.WriteLine("Employee Name: " + dr1.GetString(0) + ", " + "Employee Dept:" + dr1.GetDecimal(1)); Console.WriteLine("Employee Name: " + dr2.GetString(0) + ", " + "Employee Dept:" + dr2.GetDecimal(1)); Console.WriteLine(); } Console.WriteLine("Press 'Enter' to continue"); Console.ReadLine(); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.InnerException); Console.WriteLine(ex.Data); } } } } 

试试这个动态sql ……

 DECLARE type id_array IS TABLE OF number; t_Ids id_array; BEGIN UPDATE CSM_RECORDS SET MIGRATION_STATE = 1, LAST_UPDATE = CURRENT_DATE WHERE OBJECT_UID IN (SELECT OBJECT_UID FROM CSM_RECORDS obj WHERE MIGRATION_STATE = 0 AND ROWNUM <= :BatchSize) RETURNING OBJECT_UID BULK COLLECT INTO t_Ids; SELECT * FROM CSM_RECORDS;-- WHERE OBJECT_UID IN (t_Ids); END; 

感谢大家的反馈。 事实certificate，在调试此问题时，我在某些时候切换了定义我的查询的变量，因此在运行应用程序时实际上并未应用我对查询所做的更改。 结果，我不确定究竟哪个修复得到了答案。 但只是为了完整性，这就是我最终使用的工作方式。

第一次运行：

 create type id_array as table of number; 

（从这个问题的答案）

查询：

 DECLARE t_Ids id_array; BEGIN UPDATE CSM_RECORDS SET MIGRATION_STATE = 1, LAST_UPDATE = CURRENT_DATE WHERE OBJECT_UID IN (SELECT OBJECT_UID FROM CSM_RECORDS obj WHERE MIGRATION_STATE = 0 AND ROWNUM <= :BatchSize AND (:BatchName IS NULL OR obj.BATCH_NAME = :BatchName) RETURNING OBJECT_UID BULK COLLECT INTO t_Ids; OPEN :rcursor FOR SELECT * FROM CSM_RECORDS WHERE OBJECT_UID IN (SELECT * FROM TABLE(cast(t_Ids as id_array))); END; 

而C＃看起来像：

 OracleCommand getNextNodesC = new OracleCommand(QUERY_DEFINED_ABOVE.Replace("rn", "n"), conn); getNextNodesC.BindByName = true; OracleParameter batchSizeP = new OracleParameter("BatchSize", OracleDbType.Int32); batchSizeP.Value = batchSize; getNextNodesC.Parameters.Add(batchSizeP); OracleParameter batchNameP = new OracleParameter("BatchName", OracleDbType.Varchar2); batchNameP.Value = batchName; getNextNodesC.Parameters.Add(batchNameP); OracleParameter returnCursor = new OracleParameter("rcursor", OracleDbType.RefCursor); returnCursor.Direction = ParameterDirection.ReturnValue; getNextNodesC.Parameters.Add(returnCursor); return getNextNodesC.ExecuteReader(); 

b_levitt关于光标绑定变量上缺少冒号的建议虽然是现场开启的。 我还需要用“ n”替换查询中的所有“ r n”。

我还不清楚为什么我需要运行CREATE TYPE...而不仅仅是使用DECLARE TYPE...因为后者似乎对块的BULK COLLECT部分工作正常，但它现在似乎工作正常。

再次感谢您的帮助。

上述就是C#学习教程：从C＃中的PL / SQL匿名块返回游标分享的全部内容，如果对大家有所用处且需要了解更多关于C#学习教程，希望大家多多关注---计算机技术网(www.ctvol.com)!