如何使用来自 C# ODP.NET 的 Oracle Ref Cursor 作为返回值参数，而不使用存储函数或过程?

问题描述

我需要帮助了解我是否尝试使用 Ref Cursor 作为多个记录/值的返回值参数的方式，PL/SQL 只是 OracleCommand 对象的 CommandText 而不是存储过程或函数, 甚至是可能的.

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.

如果这是不可能的，我想要做的是找到一种方法来发出 PL/SQL 语句，该语句将更新未知数量的记录(取决于与 WHERE 子句匹配的记录数)，并返回 Id在 OracleDataReader 中更新的所有记录，使用到数据库的单次往返，而不使用存储过程或函数.

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.

我正在使用 ODP.NET 使用 Oracle 11g 与使用 SQL 连接检索/修改数据的现有 C# .NET 4.0 代码库进行通信.我使用的简化测试表定义如下所示:

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:

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;

从名为 Oracle_UpdateWorkerStatus2 的脚本文件加载并包含在 OracleCommand.CommandText 中的现有 SQL 语句如下所示:

The existing SQL statement, loaded from a script file named Oracle_UpdateWorkerStatus2, and contained in the OracleCommand.CommandText looks like so:

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;

我创建了一个小的 C# 测试程序来尝试隔离出现 ORA-01036非法变量名称/编号"错误的地方，该错误的主体如下所示:

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:

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:
{0}
Source:
{1}
", err.Message, err.Source);
                    System.Diagnostics.Debug.WriteLine("Message:
{0}
Source:
{1}
", err.Message, err.Source);
                }
            }
            cn.Close();
        }
        Console.WriteLine("Press Any Key To Exit.
");
        Console.ReadKey(false);
    }
  }
}

我已经尝试更改参数名称、命名和不命名 UpdatedRecords 参数、更改顺序使 UpdatedRecords 是第一个或最后一个.到目前为止，我发现的最接近的是以下 StackOverflow 问题(如何从 C# 调用带有 Ref Cursor 作为输出参数的 Oracle 函数?)，但据我所知，它仍然使用存储函数.

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.

从 SQL Developer 运行 Oracle_UpdateWorkerStatus2 PL/SQL 脚本，它打开输入绑定"对话框，我在其中输入 CurentStateId、StateId 和 StateReasonId 的值，如上面的代码所示，但它给出了以下错误报告:

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:

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:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

当我定义了 WorkerStatus 表并将 id_array 类型的 t_Ids 变量声明为表时，我真的不明白为什么它告诉我该表不存在.非常感谢这里的任何帮助.

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.

推荐答案

我会尝试一个答案而不是另一个评论.

I will try an answer instead of another comment.

正如我在一条评论中所说，纯/简单的 select 语句在 PL/SQL 中不起作用.但是我说错了，你需要一个存储函数来返回一个引用游标.

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.

但首先要注意的是:您在 PL/SQL 块中声明的id_array"类型是 PL/SQL 类型.它不能用于引用游标选择语句.相反，您将需要一个 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:

create type id_array as table of number;

这只需执行一次，就像创建表"一样.

This needs to be executed only once, just like a "create table".

您的 PL/SQL 块可能如下所示:

Your PL/SQL-block could then look like this:

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;

附注:
在组装这篇文章时，我意识到 ORA-00942 可能来自哪里.数组 t_ids 基于 PL/SQL 类型，该类型在 SQL 端未知/可用.

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.

这篇关于如何使用来自 C# ODP.NET 的 Oracle Ref Cursor 作为返回值参数，而不使用存储函数或过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！