我需要帮助的理解，如果这样，我试图用一个参考光标作为一个返回值参数的多个记录/值，与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子句），并返回标识所有记录更新在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.

我用的Oracle 11g使用ODP.NET与现有的C＃.NET 4.0 code-基础，使用SQL连接来检索/修改数据通信工作。我使用的是看起来像这样简化的测试表的定义：

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

我pre-填充表像这样三个测试值：

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;

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

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:\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);
    }
  }
}

我试图改变参数名，命名和未命名的UpdatedRecords参数，改变为了使UpdatedRecords是第一个或最后一个。我到目前为止发现的最接近的是下面的StackOverflow问题（How从C＃调用一个Oracle函数中Ref游标作为输出参数？），但仍然采用了存储功能，据我可以告诉。

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的值在code以上，但它提供了以下错误报告：

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

我真的不明白为什么它告诉我的表不存在类型id_array，当我已经定义了WorkerStatus表，并宣布t_Ids变量，是一个表也。任何帮助是极大的AP preciated。

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.

但是，首先第一件事情：id_array你在PL / SQL块声明的类型是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;

PS： 虽然组装这篇文章中，我意识到其中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.