如何使用c# ODP中的Oracle Ref游标。NET作为返回值参数，而不使用存储函数或过程?

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游标作为多个记录/值的ReturnValue参数(PL/SQL只是OracleCommand对象的命令文本，而不是存储过程或函数中的命令文本)的方式是可能的，我需要帮助理解这一点。

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与Oracle 11g合作。NET用于与现有的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:

现有的SQL语句，从名为Oracle_UpdateWorkerStatus2的脚本文件中加载，并包含在OracleCommand中。CommandText看起来像这样:

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 * 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。到目前为止，我发现的最接近的事情是下面的*问题(如何用一个Ref游标作为c#的外参数调用Oracle函数)，但据我所知，它仍然使用一个存储函数。

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变量声明为表时，我真的不明白为什么它会告诉我该表不存在。非常感谢您的帮助。

1 个解决方案

#1

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中不能工作。但是我说错了，您需要一个存储函数来返回一个ref游标。

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类型。它不能用于ref游标选择语句。相反，您将需要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端不知道/可用。

#1