使用用户定义的类型参数调用函数 (Oracle ODP.NET)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4162364/
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
Calling a function with user defined type parameters (Oracle ODP.NET)
提问by maephisto
I'm using a function :
我正在使用一个函数：
fu_has_permissions(udt_person('johny','superman'),'fly_away')
udt_person is a user defined type :
udt_person 是用户定义的类型：
create or replace TYPE udt_person AS OBJECT
(name VARCHAR2(3),
id VARCHAR2(18));
I want to use bind variables whan calling this function, but i'm not really sure what am i doing wrong ... Here's the code :
我想在调用这个函数时使用绑定变量，但我不确定我做错了什么......这是代码：
......
OracleParameter udtPersParam = new OracleParameter();
udtPersParam.ParameterName = ":pUdtPers";
udtPersParam.UdtTypeName = "UDT_PERS";
string[] paramValues = { name, id };
udtPersParam.Value = paramValues;
OracleParameter pAction = new OracleParameter(":pAction", OracleDbType.Varchar2, 255);
pAction.Value = action;
parameters.Add(udtPartParam);
parameters.Add(pAction);
try
{
_loginOdr = DBFacade.ExecuteSelectQuery("select fu_has_permissions(:pUdtPart, :pAction) from dual", parameters);
}
Thanks!
谢谢！
回答by Harrison
the udt type must be a class that implements IOracleCustomType and/or IOracleCustomTypeFactory, IOracleArrayTypeFactory
udt 类型必须是实现 IOracleCustomType 和/或 IOracleCustomTypeFactory、IOracleArrayTypeFactory 的类
unfortuntately you cannot just create a string array and pass it in
不幸的是，您不能只创建一个字符串数组并将其传入
look in the odp.net samples that come with the odp installation %ora_home%\client_1\odp.net\samples\4\UDT
查看 odp 安装附带的 odp.net 示例 %ora_home%\client_1\odp.net\samples\4\UDT
also check out these links for samples and walkthroughs http://developergeeks.com/article/35/working-with-user-defined-type-oracle-udt-and-custom-type-using-odp-net-11ghttp://www.codeproject.com/KB/database/ORACLE_UDT.aspx and http://st-curriculum.oracle.com/obe/db/hol08/dotnet/udt/udt_otn.htm
还可以查看这些示例和演练链接 http://developergeeks.com/article/35/working-with-user-defined-type-oracle-udt-and-custom-type-using-odp-net-11ghttp: //www.codeproject.com/KB/database/ORACLE_UDT.aspx 和 http://st-curriculum.oracle.com/obe/db/hol08/dotnet/udt/udt_otn.htm
回答by PeteH
if I were you I'd have a look at the ODP add-in to Visual Studio. With this you can connect to your database, select a UDT on the database, and "Generate Custom Class" to get a .net class you can use.
如果我是你，我会看看 Visual Studio 的 ODP 插件。有了它，您可以连接到您的数据库，在数据库上选择一个 UDT，然后“生成自定义类”以获取您可以使用的 .net 类。
Look inside the class and you'll see what Harrison means. Pay particular attention to the OracleObjectMappingAttributes on top of the properties, and the overrides of To/FromCustomObject.
看看课堂内部，你就会明白哈里森的意思。请特别注意属性顶部的 OracleObjectMappingAttributes，以及 To/FromCustomObject 的覆盖。
When you construct your OracleCommand, the OracleParameter.Value needs to be a class of this type.
当您构造 OracleCommand 时，OracleParameter.Value 需要是这种类型的类。
That should get you started at a high level. A word of warning, though. The code generated by ODP is ugly to say the least - we're on the point of ditching all ODP-generated classes in our own scenario. But you'll need to understand what things like IOracleCustomType, IOracleCustomTypeFactory, IOracleArrayTypeFactory, INullable are before you'll be in a position to do this.
这应该能让你从高层次开始。不过，还是要警告一下。ODP 生成的代码至少可以说是丑陋的——我们即将在我们自己的场景中抛弃所有 ODP 生成的类。但是，在您能够执行此操作之前，您需要了解 IOracleCustomType、IOracleCustomTypeFactory、IOracleArrayTypeFactory、INullable 之类的内容。
Incidentally since your specific question surrounds arrays, you might want to look at Oracle NTYPEs here, rather than TYPEs.
顺便说一句，由于您的具体问题围绕数组，您可能希望在此处查看 Oracle NTYPE，而不是 TYPE。
回答by Alex Poole
Don't know anything about ODP.Net really, but the error suggests that it doesn't like you trying to use a string array as the value for an Oracle parameter. Which doesn't sound unreasonable.
对 ODP.Net 一无所知，但错误表明它不喜欢您尝试使用字符串数组作为 Oracle 参数的值。这听起来不无道理。
A quick google of 'odp.net object varchar2' gave this OTN forum postas the first result; it includes an example of using an object about half-way down, including converting to and from Oracle object types.
'odp.net object varchar2' 的快速 google 给出了这个 OTN 论坛帖子作为第一个结果；它包括一个使用大约一半的对象的示例，包括在 Oracle 对象类型之间进行转换。
