过程参数中对Oracle UDT的无效绑定 [英] Invalid binding for Oracle UDT in procedure parameter
问题描述
我正在尝试调用将table of numbers
的自定义数据类型作为参数之一的过程.
I'm attempting to call a procedure that takes a custom data type of table of numbers
as one of the parameters.
这是类型的定义:
create type num_list as table of number;
过程的定义:
create or replace procedure my_procedure
(listofnumbers num_list,
v_value char)
is
begin
update my_table
set my_column = v_value
where my_row_id in (select column_value
from table(listofnumbers));
end;
使用ODP.NET和C#,我将其声明如下:
Using ODP.NET and C#, I'm declaring it as follows:
var row_ids = new int[] { 1, 2 };
using (var oracleConn = new Oracle.DataAccess.Client.OracleConnection(myConnectionString))
{
oracleConn.Open();
var cmd = new Oracle.DataAccess.Client.OracleCommand("my_procedure", oracleConn);
cmd.CommandType = CommandType.StoredProcedure;
var param1 = new Oracle.DataAccess.Client.OracleParameter("listofnumbers", Oracle.DataAccess.Client.OracleDbType.Array, ParameterDirection.Input);
param1.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
param1.UdtTypeName = "num_list";
param1.Value = row_ids;
cmd.Parameters.Add(param1);
var param2 = new Oracle.DataAccess.Client.OracleParameter("v_value ", Oracle.DataAccess.Client.OracleDbType.Char, ParameterDirection.Input);
param2.Value = "Y";
cmd.Parameters.Add(param2);
cmd.ExecuteNonQuery();
}
抛出异常的状态:
无效的参数绑定参数 名称:listofnumbers
Invalid parameter binding Parameter name: listofnumbers
在定义参数时我缺少哪些属性?
What properties am I missing in defining the parameter?
推荐答案
5月14日
由于我的答案不被接受,下面是一个可能有用的链接:
As my answer got unaccepted, here is a link that might be of use:
不幸的是,由于我有不支持UDT的x64 ODP.NET,因此无法解决这个问题.
Unfortunately I cannot play around with this as I have x64 ODP.NET which does not support UDT.
但是,由于您的UDT是集合,您是否尝试在param1上设置Size属性?
However, as your UDT is a collection did you try setting the Size property on param1?
param1.Size = row_ids.Length;
现在,它不是UDT,但这是一个代码片段,该代码片段是我如何绑定到程序包规范中定义的类型的代码,如下所示:
Now, it is not a UDT, but here is a code snippet how I bind to a type defined in a package spec as:
TYPE t_stringlist IS TABLE OF VARCHAR2(4000);
string[] values = new string[] { "AAA", "BBB" };
OracleParameter parameter = new OracleParameter();
parameter.Name = "my_param";
parameter.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
parameter.OracleDbType = OracleDbType.Varchar2;
parameter.ArrayBindSize = new int[values.Length];
parameter.ArrayBindStatus = new OracleParameterStatus[values.Length];
parameter.Size = values.Length;
for (int i = 0; i < values.Length; ++i)
{
parameter.ArrayBindSize[i] = 4000;
parameter.ArrayBindStatus[i] = OracleParameterStatus.Success;
}
parameter.Value = values;
我不确定您可以将ArrayBindSize设置为什么值.
I am not sure what values you might set ArrayBindSize to.
此外,您可以考虑为UDT创建类型工厂:
Also, you might consider creating a type factory for your UDT:
http://download.oracle.com/docs/html/E15167_01/featUDTs.htm
无论如何,我希望您能在这里找到有用的东西.
Anyway, I hope you find something here that helps.
这篇关于过程参数中对Oracle UDT的无效绑定的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!