Oracle.DataAccess(ODP.NET)阵列绑定“值未在预期范围内" [英] Oracle.DataAccess (ODP.NET) Array Binding "Value does not fall within the expected range"

问题描述

我的情况

我正在将ODP.NET oracle提供程序与c#3.5配合使用，并且我正在尝试将数组作为过程的参数传递...像这样:

var paramNames = new OracleParameter();
paramNames.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
paramNames.ParameterName = "P_JOB_TITLE";
paramNames.Size = 2;
paramNames.Value =  new string[2]{ "name1", "name1" };
cmd.Parameters.Add(paramNames);

当运行时代码转到paramNames.Value =新字符串[2] {"name1"，"name1"};它抓住了这个错误

值不在预期范围内"

任何人都可以修复它吗?

其他信息

指定OracleDbType错误是固定的，但是执行会给我一些错误

paramNames.OracleDbType = OracleDbType.Varchar2;

无法将类型为'System.String []'的对象转换为类型为'System.IConvertible'."

我的目标是做这样的事情

http://aspalliance.com/621_Using_ODPNET_to_Insert_Multiple_Rows_Trips_3

另一个没有参数的问题

插入这样的out参数

            paramNames = new OracleParameter();
            paramNames.ParameterName = "O_JOB_ID";
            paramNames.Size = 3;
            paramNames.Direction = ParameterDirection.Output;
            paramNames.OracleDbType = OracleDbType.Int32;
            paramNames.Value = new int[3] { 0, 0, 0 }; ;
            cmd.Parameters.Add(paramNames);

它在ExecuteNonQuery完成时正确填充.例如，pls-sql过程执行3次插入，我返回每个数组记录的row-id.

但是我出了点问题，例如，在第二行设置了一个小错误，整个OUT参数(数组)始终设置为0.我希望至少params [0] .value会得到增强

谢谢

解决方案

我认为您正在尝试合并 Array Bind ({简单地将数组绑定到参数以使其多次执行-这就是您提供的链接中的示例的用法}，它使用了 Associative Array (使用数组的INPUT参数的PLSQLAssociativeArray).

由于您没有发布正在运行的包/过程，因此我假设您正在执行类似的操作(只是将其放下来以验证假设)

procedure insertdata(P_JOB_TITLE IN VARCHAR2) as
begin
insert into myTable(x) value (P_JOB_TITLE);
end  insertdata;

要像文章的作者一样执行此操作，您需要使用 ArrayBindCount (查看此链接，还有一个示例). 这也表明，如果您有多个参数，则每个参数都会期望一个ARRAY.

现在对传入的所有P_JOB_TITLE()执行此操作

//this was missing in your example and MUST be there to tell ODP how many array elements to expect
cmd.ArrayBindCount = 2;

 string[] jobTitleArray = {"name1", "name1"};

OracleParameter paramNames= new OracleParameter("P_JOB_TITLE", OracleDbType.Varchar2);

   //paramNames.CollectionType = OracleCollectionType.PLSQLAssociativeArray;/*once again, you are passing in an array of values to be executed and not a pl-sql table*/

    //paramNames.Size = 2; /* this is unnecessary since it is for a plsql-associative array*/
    paramNames.Value =  jobTitleArray ;
    cmd.Parameters.Add(paramNames);

---

对于一个plSQLAssociativeArray示例，请查看安装ODP @％ORA_HOME％\ odp.net \ samples \ 2.x \ AssocArray时提供的示例

以及数组绑定示例(如您提供的链接中所示) @％ORA_HOME％\ odp.net \ samples \ 2.x \ ArrayBind

my scenario

i'm using ODP.NET oracle provider with c# 3.5, and i am trying to pass an array as parameter for a procedure...like this:

var paramNames = new OracleParameter();
paramNames.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
paramNames.ParameterName = "P_JOB_TITLE";
paramNames.Size = 2;
paramNames.Value =  new string[2]{ "name1", "name1" };
cmd.Parameters.Add(paramNames);

when runtime code goes to paramNames.Value = new string[2]{ "name1", "name1" }; it catch with this error

"Value does not fall within the expected range"

Can anyone fix it?

ADDITIONAL INFO

Specifying OracleDbType the error is fixed, but executing give me some errors

paramNames.OracleDbType = OracleDbType.Varchar2;

"Unable to cast object of type 'System.String[]' to type 'System.IConvertible'."

my goal is to do something like this

http://aspalliance.com/621_Using_ODPNET_to_Insert_Multiple_Rows_within_a_Single_Round_Trip.3

ANOTHER PROBLEM WITH OUT PARAMETER

Inserting an out parameter like this

            paramNames = new OracleParameter();
            paramNames.ParameterName = "O_JOB_ID";
            paramNames.Size = 3;
            paramNames.Direction = ParameterDirection.Output;
            paramNames.OracleDbType = OracleDbType.Int32;
            paramNames.Value = new int[3] { 0, 0, 0 }; ;
            cmd.Parameters.Add(paramNames);

it is correctly filled when ExecuteNonQuery finished. For example the pls-sql procedure performs 3 inserts and i return the row-id of each array record.

But i something goes wrong, for example isnerting the 2nd row, the entire OUT parameters (array) are always set on 0. I expected at least the params[0].value was enhanced

Thanks

解决方案

I think you are trying to merge an Array Bind {simply binding an array to a param to have it execute multi times -- this is how the example in the link you provided did it} with an Associative Array {re: PLSQLAssociativeArray with an INPUT param of TABLE OF}.

Since you didn't post your package/proc that you are running, I am assuming you are doing something like this (just putting this down to validate the assumption)

procedure insertdata(P_JOB_TITLE IN VARCHAR2) as
begin
insert into myTable(x) value (P_JOB_TITLE);
end  insertdata;

To execute this like the author of the article you need to use ArrayBindCount (check out this link, it also has an example). This also indicates, if you have multiple parameters, it will expect an ARRAY for each one.

Now to have this executed for all the P_JOB_TITLE() that you pass in

//this was missing in your example and MUST be there to tell ODP how many array elements to expect
cmd.ArrayBindCount = 2;

 string[] jobTitleArray = {"name1", "name1"};

OracleParameter paramNames= new OracleParameter("P_JOB_TITLE", OracleDbType.Varchar2);

   //paramNames.CollectionType = OracleCollectionType.PLSQLAssociativeArray;/*once again, you are passing in an array of values to be executed and not a pl-sql table*/

    //paramNames.Size = 2; /* this is unnecessary since it is for a plsql-associative array*/
    paramNames.Value =  jobTitleArray ;
    cmd.Parameters.Add(paramNames);

---

For a plSQLAssociativeArray example have a look at the samples provided when you installed ODP @ %ORA_HOME%\odp.net\samples\2.x\AssocArray

and for array bind examples (as from the link you provided) @ %ORA_HOME%\odp.net\samples\2.x\ArrayBind

这篇关于Oracle.DataAccess(ODP.NET)阵列绑定“值未在预期范围内"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！