Returning cursor from PL/SQL anonymous block in C#

Question

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.

My query is defined as:

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

You can see I've commented out the WHERE clause on the cursor in an attempt just to get anything to return at all.

Over on the C# side, I've got:

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

The end goal is a DbDataReader that I can use, but in the above code, the returnCursor.Value seems to remain null. I've tried various combinations of Output vs. ReturnValue parameters and ExecuteNonQuery() and ExecuteReader() to no avail.

Any pointers would be appreciated, but an example of code that would actually accomplish what I'm looking for would be spectacular.

Answer 1

TLDR: You're missing the colon on your cursor bind variable:

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

Full answer: As you probably know, in oracle there is the pl/sql context (your anonymous block) and the SQLplus context. Here is a block with SQLplus vars, the block with the related bind variables, and a SQLplus print at the end:

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

When executing a block in .net, ODP.net is taking care of the preparation done at the SQLplus level. Here is the same block executed from .net (as an nunit test):

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

  }
}

You can read more here: http://www.brothersincode.com/post/executing-SQL-Plus-Batches-from-Net.aspx