title: Returning cursor from PL/SQL anonymous block in C#
body: <p>I'm working on converting an existing application from SQL Server over to Oracle, and I've hit a roadblock. I'm trying to execute an anonymous block as dynamic SQL and return a result set. However nothing I've tried seems to be able to return any values. Stored procedures are out due to design constraints.</p>
  
  <p>My query is defined as:</p>
  
  <pre><code>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 &lt;= :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 (t_Ids);
  END;
  </code></pre>
  
  <p>You can see I've commented out the WHERE clause on the cursor in an attempt just to get <em>anything</em> to return at all.</p>
  
  <p>Over on the C# side, I've got:</p>
  
  <pre><code>OracleCommand getNextNodesC = new OracleCommand(SQL_AS_SHOWN_ABOVE, 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.Output;
  getNextNodesC.Parameters.Add(returnCursor);
  
  getNextNodesC.ExecuteNonQuery();
  
  return ((Oracle.ManagedDataAccess.Types.OracleRefCursor)returnCursor.Value).GetDataReader();
  </code></pre>
  
  <p>The end goal is a <code>DbDataReader</code> that I can use, but in the above code, the <code>returnCursor.Value</code> seems to remain null. I've tried various combinations of <code>Output</code> vs. <code>ReturnValue</code> parameters and <code>ExecuteNonQuery()</code> and <code>ExecuteReader()</code> to no avail.</p>
  
  <p>Any pointers would be appreciated, but an example of code that would actually accomplish what I'm looking for would be spectacular.</p>