Oracle.DataAccess (ODP.NET) 数组绑定“值不在预期范围内”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6411820/
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
Oracle.DataAccess (ODP.NET) Array Binding "Value does not fall within the expected range"
提问by user756037
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:
我正在使用带有 c# 3.5 的 ODP.NET oracle 提供程序，并且我试图将数组作为参数传递给一个过程......像这样：
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
当运行时代码转到 paramNames.Value = new string[2]{ "name1", "name1" }; 它抓住了这个错误
"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
指定 OracleDbType 错误是固定的，但执行给我一些错误
paramNames.OracleDbType = OracleDbType.Varchar2;
"Unable to cast object of type 'System.String[]' to type 'System.IConvertible'."
“无法将“System.String[]”类型的对象转换为“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
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.
当 ExecuteNonQuery 完成时它被正确填充。例如，pls-sql 过程执行 3 次插入，我返回每个数组记录的行 ID。
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
但是我出了点问题，例如第二行，整个 OUT 参数（数组）总是设置为 0。我预计至少 params[0].value 得到了增强
Thanks
谢谢
回答by Harrison
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}.
我认为您正在尝试合并一个数组绑定{简单地将一个数组绑定到一个参数以使其执行多次 - 这就是您提供的链接中的示例是如何做到的} 与一个关联数组{re: PLSQLAssociativeArray with an INPUT 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.
要像文章作者一样执行此操作，您需要使用ArrayBindCount（查看此链接，它也有一个示例）。这也表明，如果您有多个参数，则每个参数都需要一个 ARRAY。
Now to have this executed for all the P_JOB_TITLE() that you pass in
现在为您传入的所有 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);
For a plSQLAssociativeArray example have a look at the samples provided when you installed ODP @ %ORA_HOME%\odp.net\samples\2.x\AssocArray
对于 plSQLAssociativeArray 示例，请查看安装 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
和数组绑定示例（来自您提供的链接）@ %ORA_HOME%\odp.net\samples\2.x\ArrayBind
