如何使用来自 C# ODP.NET 的 Oracle Ref Cursor 作为返回值参数，而不使用存储函数或过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11267746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use an Oracle Ref Cursor from C# ODP.NET as a ReturnValue Parameter, without using a Stored Function or Procedure?
提问by Paul
I need help understanding if the way I'm trying to use a Ref Cursor as a ReturnValue Parameter for multiple records/values, with the PL/SQL just being the CommandText of an OracleCommand object and not in a Stored Procedure or Function, is even possible.
我需要帮助了解我是否尝试使用 Ref Cursor 作为多个记录/值的返回值参数的方式，PL/SQL 只是 OracleCommand 对象的 CommandText 而不是存储过程或函数，甚至可能的。
If that is not possible, what I'm trying to do is find a way to issue a PL/SQL statement that will Update an unknown number of records (depends on how many match the WHERE clause), and return the Ids of all the records Updated in an OracleDataReader, using a single round-trip to the database, without the use of a Stored Procedure or Function.
如果这是不可能的，我想要做的是找到一种方法来发出一个 PL/SQL 语句，该语句将更新未知数量的记录（取决于有多少匹配 WHERE 子句），并返回所有的 Id记录 在 OracleDataReader 中更新，使用到数据库的单次往返，而不使用存储过程或函数。
I'm working with Oracle 11g using ODP.NET for communication with an existing C# .NET 4.0 code-base that uses the SQL connection to retrieve/modify data. The simplified test table definition I'm using looks like so:
我正在使用 ODP.NET 使用 Oracle 11g 与现有的 C# .NET 4.0 代码库进行通信，该代码库使用 SQL 连接来检索/修改数据。我使用的简化测试表定义如下所示：
CREATE TABLE WorkerStatus
(
Id NUMERIC(38) NOT NULL
,StateId NUMERIC(38) NOT NULL
,StateReasonId NUMERIC(38) NOT NULL
,CONSTRAINT PK_WorkerStatus PRIMARY KEY ( Id )
)
I pre-populate the table with three test values like so:
我用三个测试值预先填充表格，如下所示：
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
VALUES (1, 0, 0)';
EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
VALUES (2, 0, 0)';
EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
VALUES (3, 0, 0)';
END;
The existing SQL statement, loaded from a script file named Oracle_UpdateWorkerStatus2, and contained in the OracleCommand.CommandText looks like so:
从名为 Oracle_UpdateWorkerStatus2 的脚本文件加载并包含在 OracleCommand.CommandText 中的现有 SQL 语句如下所示：
DECLARE
TYPE id_array IS TABLE OF WorkerStatus.Id%TYPE INDEX BY PLS_INTEGER;
t_ids id_array;
BEGIN
UPDATE WorkerStatus
SET
StateId = :StateId
,StateReasonId = :StateReasonId
WHERE
StateId = :CurrentStateId
RETURNING Id BULK COLLECT INTO t_Ids;
SELECT Id FROM t_Ids;
END;
I've created a small C# test program to attempt to isolate where I'm getting an ORA-01036 "illegal variable name/number" error that has a main body that looks like so:
我创建了一个小的 C# 测试程序来尝试隔离出现 ORA-01036“非法变量名称/编号”错误的地方，该错误的主体如下所示：
using System;
using System.Configuration;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace OracleDbTest
{
class Program
{
static void Main(string[] args)
{
// Load the SQL command from the script file.
StringBuilder sql = new StringBuilder();
sql.Append(Properties.Resources.Oracle_UpdateWorkerStatus2);
// Build and excute the command.
OracleConnection cn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleSystemConnection"].ConnectionString);
using (OracleCommand cmd = new OracleCommand(sql.ToString(), cn))
{
cmd.BindByName = true;
cn.Open();
OracleParameter UpdatedRecords = new OracleParameter();
UpdatedRecords.OracleDbType = OracleDbType.RefCursor;
UpdatedRecords.Direction = ParameterDirection.ReturnValue;
UpdatedRecords.ParameterName = "rcursor";
OracleParameter StateId = new OracleParameter();
StateId.OracleDbType = OracleDbType.Int32;
StateId.Value = 1;
StateId.ParameterName = "StateId";
OracleParameter StateReasonId = new OracleParameter();
StateReasonId.OracleDbType = OracleDbType.Int32;
StateReasonId.Value = 1;
StateReasonId.ParameterName = "StateReasonId";
OracleParameter CurrentStateId = new OracleParameter();
CurrentStateId.OracleDbType = OracleDbType.Int32;
CurrentStateId.Value = 0;
CurrentStateId.ParameterName = "CurrentStateId";
cmd.Parameters.Add(UpdatedRecords);
cmd.Parameters.Add(StateId);
cmd.Parameters.Add(StateReasonId);
cmd.Parameters.Add(CurrentStateId);
try
{
cmd.ExecuteNonQuery();
OracleDataReader dr = ((OracleRefCursor)UpdatedRecords.Value).GetDataReader();
while (dr.Read())
{
Console.WriteLine("{0} affected.", dr.GetValue(0));
}
dr.Close();
}
catch (OracleException e)
{
foreach (OracleError err in e.Errors)
{
Console.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
System.Diagnostics.Debug.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
}
}
cn.Close();
}
Console.WriteLine("Press Any Key To Exit.\n");
Console.ReadKey(false);
}
}
}
I've tried changing the parameter names, naming and not-naming the UpdatedRecords parameter, changing the order so the UpdatedRecords is first or last. The closest thing I've found so far is the following StackOverflow question (How to call an Oracle function with a Ref Cursor as Out-parameter from C#?), but that still uses a Stored Function as far as I can tell.
我尝试更改参数名称，命名和不命名 UpdatedRecords 参数，更改顺序使 UpdatedRecords 是第一个或最后一个。到目前为止，我发现的最接近的问题是以下 StackOverflow 问题（How to call an Oracle function with a Ref Cursor as Out-parameter from C#?），但据我所知，它仍然使用存储函数。
Running the Oracle_UpdateWorkerStatus2 PL/SQL script from SQL Developer, it opens the "Enter Binds" dialog where I enter the values for CurentStateId, StateId and StateReasonId as in the code above, but it gives the following error report:
从 SQL Developer 运行 Oracle_UpdateWorkerStatus2 PL/SQL 脚本，它打开“输入绑定”对话框，我在其中输入 CurentStateId、StateId 和 StateReasonId 的值，如上面的代码，但它给出了以下错误报告：
Error report:
ORA-06550: line 13, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 13, column 2:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I don't really understand why it's telling me the table doesn't exist, when I've defined the WorkerStatus table, and declared the t_Ids variable, of type id_array, to be a table as well. Any help here is greatly appreciated.
当我定义了 WorkerStatus 表并将 id_array 类型的 t_Ids 变量也声明为表时，我真的不明白为什么它告诉我该表不存在。非常感谢这里的任何帮助。
采纳答案by Juergen Hartelt
I will try an answer instead of another comment.
我会尝试一个答案而不是另一个评论。
As I said in one comment, a pure/simple select-statement does not work in PL/SQL. But I was wrong in stating, that you need a stored function to return a ref cursor.
正如我在一条评论中所说，纯/简单的 select 语句在 PL/SQL 中不起作用。但是我说错了，你需要一个存储函数来返回一个引用游标。
But first things first: The type "id_array" you declare in your PL/SQL-block is a PL/SQL type. It cannot be used in a ref cursor select statement. Instead you will need a SQL type:
但首先要注意的是：您在 PL/SQL 块中声明的“id_array”类型是 PL/SQL 类型。它不能用于引用游标选择语句。相反，您将需要一个 SQL 类型：
create type id_array as table of number;
This needs to be executed only once, just like a "create table".
这只需执行一次，就像“创建表”一样。
Your PL/SQL-block could then look like this:
您的 PL/SQL 块可能如下所示：
DECLARE
t_ids id_array;
BEGIN
UPDATE WorkerStatus
SET
StateId = :StateId
,StateReasonId = :StateReasonId
WHERE
StateId = :CurrentStateId
RETURNING Id BULK COLLECT INTO t_Ids;
OPEN :rcursor FOR SELECT * FROM TABLE(cast(t_Ids as id_array));
END;
PS:
While assembling this post, I realized where the ORA-00942 might come from. The array t_ids was based on a PL/SQL type, which is not known/available on the SQL side.
PS：
在组装这篇文章时，我意识到 ORA-00942 可能来自哪里。数组 t_ids 基于 PL/SQL 类型，该类型在 SQL 端未知/不可用。
