Oracle 存储过程和自定义数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4098944/
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 Stored Procedure and custom data type
提问by Omar
I have an Oracle stored procedure which takes two parameters: a custom data type and a string.
我有一个 Oracle 存储过程，它采用两个参数：自定义数据类型和字符串。
Calling the stored procedure in Oracle, I would do the following:
在 Oracle 中调用存储过程，我将执行以下操作：
EXECUTE MY_STORED_PROCEDURE(MYTYPE_T(99, 231), 'mystring')
How can I execute this using C#? I understand that I need to setup the command to be a stored procedure, but how do I specify the first parameter as custom data type?
如何使用 C# 执行此操作？我知道我需要将命令设置为存储过程，但是如何将第一个参数指定为自定义数据类型？
Update:
更新：
MYTYPE_Tis TABLE OF NUMBERcreated via
MYTYPE_T是TABLE OF NUMBER通过创建的
CREATE OR REPLACE TYPE mytype_t AS TABLE OF NUMBER ;
采纳答案by Harrison
You won't be able to do this easily with the deprecated System.Data.OracleClientbut you can utilize oracle's ODPwith using UDTs. If that is not an option, I am unsure how you can do it via parameters in C# with System.Data.
您将无法使用已弃用的 System.Data.OracleClient轻松完成此操作， 但您可以通过使用 UDT来利用oracle 的 ODP。如果这不是一个选项，我不确定如何使用 System.Data 通过 C# 中的参数来实现。
ODP does come with a lot of examples and there are examples in the above links.
ODP 确实有很多例子，上面的链接中有例子。
I am going to add some more links that will hopefully help:
我将添加更多链接，希望能有所帮助：
- visual studio ODP index
- this shows you exactly how to utilize the ODT to create you custom class wrappers and call them(do note that this is midway through, they walk through using the tool to create the custom types above it in the example -- this walkthrough is quite thorough and should get you directly where you need to be)
- Download: now this guy also installs sample files, this is another terrific example of exactly what you need to do: once installed goto [directory path you install]..\product\11.2.0\client_1\odp.net\samples\4\UDT\object1.cs
- 视觉工作室ODP指数
- 这向您展示了如何利用 ODT 创建自定义类包装器并调用它们（请注意，这是中途，他们通过使用该工具在示例中创建其上方的自定义类型进行了演示——本演练非常彻底并且应该让你直接到达你需要去的地方）
- 下载：现在这家伙还安装了示例文件，这是您需要做的另一个很棒的示例：安装后转到 [安装的目录路径]..\product\11.2.0\client_1\odp.net\samples\4 \UDT\object1.cs
It really pays to allow the ODT tools for Visual studio to create your classes for your UDTs for you (e.g. IOracleCustomType and such) . you can then go into them and amend them to suit your needs. then once all is said and done (snippet from object1.cs):
允许 Visual Studio 的 ODT 工具为您的 UDT 创建类（例如 IOracleCustomType 等）确实值得。然后你可以进入它们并修改它们以满足你的需要。然后一旦一切都说完了（来自object1.cs的片段）：
Person p1 = new Person();
p1.Name = "John";
p1.Address = "Address1";
p1.Age = 20;
// Establish a connection to Oracle
OracleConnection con = new OracleConnection(constr);
con.Open();
// Update Person object and insert it into a database table
OracleCommand cmd = new OracleCommand(sql1, con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Object;
param1.Direction = ParameterDirection.InputOutput;
// Note: The UdtTypeName is case-senstive
param1.UdtTypeName = "SCOTT.ODP_OBJ1_SAMPLE_PERSON_TYPE";
param1.Value = p1;
cmd.Parameters.Add(param1);
also note that Person class must implement IOracleCustomType(which can be created by following the link in #2)
还要注意，Person 类必须实现IOracleCustomType（可以按照 #2 中的链接创建）
/* Person Class
An instance of a Person class represents an ODP_OBJ1_SAMPLE_PERSON_TYPE object
A custom type must implement INullable and IOracleCustomType interfaces
*/
public class Person : INullable, IOracleCustomType
The above is for a full custom type, but you are after an associative array ODP binding:
上面是一个完整的自定义类型，但你在一个关联数组 ODP 绑定之后：
http://weblogs.asp.net/ricardoperes/archive/2009/05/14/odp-net-associative-arrays.aspx
http://weblogs.asp.net/ricardoperes/archive/2009/05/14/odp-net-associative-arrays.aspx
you'll want to use
你会想要使用
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
and everything should fall into place
一切都应该到位
回答by andr
You can pass a custom datatype from C# to Oracle procedure only if this datatype is DB-defined. Have a look at this article, this should help you get started.
仅当此数据类型是 DB 定义的时，您才能将自定义数据类型从 C# 传递到 Oracle 过程。看看这篇文章，这应该可以帮助您入门。
