从 C# 中的 PL/SQL 匿名块返回游标 [英] Returning cursor from PL/SQL anonymous block in C#

问题描述

我正在将现有应用程序从 SQL Server 转换为 Oracle，但遇到了障碍.我正在尝试将匿名块作为动态 SQL 执行并返回结果集.但是，我尝试过的任何内容似乎都无法返回任何值.由于设计限制，存储过程已过时.

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.

我的查询定义为:

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;

你可以看到我已经注释掉了游标上的 WHERE 子句，只是为了让 anything 返回.

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

在 C# 方面，我有:

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

最终目标是我可以使用的 DbDataReader，但在上面的代码中，returnCursor.Value 似乎保持为空.我已经尝试了 Output 与 ReturnValue 参数和 ExecuteNonQuery() 和 ExecuteReader() 的各种组合到没有有用.

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.

推荐答案

感谢大家的反馈.事实证明，在调试此问题时，有时我切换了定义查询的变量，因此在运行应用程序时实际上并未应用我对查询所做的更改.因此，我不确定最终是哪个修复程序最终成为了答案.但为了完整起见，以下是我最终使用的有效方法.

Thanks for the feedback everyone. It turns out that while debugging this issue, at some point I switched out the variable that defined my query, so that changes I was making to the query weren't actually being applied when running the application. As a result, I'm not sure exactly which fix ended up being the answer. But just for completeness, here's what I ended up using that worked.

首先，运行:

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("
", "
"), 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 关于游标绑定变量上缺少冒号的建议是正确的.而且我还需要将查询中的所有 "替换为 ".

b_levitt's suggestion about the missing colon on the cursor bind variable was spot-on though. And I also needed to replace all the " "s in my query with " ".

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

I'm still not clear on why I need to run CREATE TYPE... rather than just using DECLARE TYPE... since the latter seems to work fine for the BULK COLLECT part of the block, but it does seem to work fine now.

再次感谢您的帮助.

这篇关于从 C# 中的 PL/SQL 匿名块返回游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！