使用用户定义的类型参数调用函数 (Oracle ODP.NET)
我正在使用一个函数：
fu_has_permissions(udt_person('johny','superman'),'fly_away')
udt_person 是一个用户定义的类型：
create or replace TYPE udt_person AS OBJECT
(name VARCHAR2(3),
id VARCHAR2(18));
我想在调用这个函数时使用绑定变量，但我不太确定我做错了什么......这是代码：
......
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);
}
谢谢！
I'm using a function :
fu_has_permissions(udt_person('johny','superman'),'fly_away')
udt_person is a user defined type :
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
udt 类型必须是实现
IOracle自定义类型
和/或 IOracleCustomTypeFactory、IOracleArrayTypeFactory
不幸的是，您不能只创建一个字符串数组并将其传递到
odp 安装附带的 odp.net 示例中
%ora_home%\client_1\odp.net\samples\4\UDT
还可以查看这些链接以获取示例和演练
http://developergeeks.com/article/35/working-with-user-define-type-oracle-udt-and-custom-type-using-odp-net-11g
http://www.codeproject.com/KB/database/ORACLE_UDT.aspx
和
http://st-curriculum.oracle.com/obe /db/hol08/dotnet/udt/udt_otn.htm
the udt type must be a class that implements
IOracleCustomType
and/or 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
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-11g
http://www.codeproject.com/KB/database/ORACLE_UDT.aspx
and
http://st-curriculum.oracle.com/obe/db/hol08/dotnet/udt/udt_otn.htm
确实对 ODP.Net 一无所知，但该错误表明它不喜欢您尝试使用字符串数组作为 Oracle 参数的值。这听起来并不是没有道理。
快速谷歌搜索“odp.net object varchar2”，得到了这篇 OTN 论坛帖子 作为第一个结果；它包括一个使用大约一半的对象的示例，包括与 Oracle 对象类型之间的转换。
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.
A quick google of 'odp.net object varchar2' gave this OTN forum post as the first result; it includes an example of using an object about half-way down, including converting to and from Oracle object types.
如果我是您，我会看看 Visual Studio 的 ODP 插件。这样您就可以连接到数据库，在数据库上选择 UDT，然后“生成自定义类”以获取可以使用的 .net 类。
看看课堂内部，你就会明白哈里森的意思。请特别注意属性顶部的 OracleObjectMappingAttributes 以及 To/FromCustomObject 的覆盖。
当您构造 OracleCommand 时，OracleParameter.Value 需要是此类型的类。
这应该能让你从高水平开始。不过，有一句警告。至少可以说，ODP 生成的代码很丑陋 - 我们即将在自己的场景中放弃所有 ODP 生成的类。但您需要先了解 IOracleCustomType、IOracleCustomTypeFactory、IOracleArrayTypeFactory、INullable 等内容，然后才能执行此操作。
顺便说一句，由于您的具体问题涉及数组，因此您可能想在这里查看 Oracle NTYPE，而不是 TYPE。
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.
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.
When you construct your OracleCommand, the OracleParameter.Value needs to be a class of this type.
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.
Incidentally since your specific question surrounds arrays, you might want to look at Oracle NTYPEs here, rather than TYPEs.