Oracle存储过程和自定义数据类型 [英] Oracle Stored Procedure and custom data type

问题描述

我有一个Oracle存储过程，该存储过程带有两个参数:自定义数据类型和字符串.

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:

EXECUTE MY_STORED_PROCEDURE(MYTYPE_T(99, 231), 'mystring')

如何使用C#执行此操作?我知道我需要将命令设置为存储过程，但是如何将第一个参数指定为自定义数据类型?

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?

MYTYPE_T是通过

MYTYPE_T is TABLE OF NUMBER created via

CREATE OR REPLACE TYPE mytype_t AS TABLE OF NUMBER ; 

推荐答案

使用 Oracle的ODP .如果不是这样，我不确定如何通过C#中的System.Data参数来实现.

You won't be able to do this easily with the deprecated System.Data.OracleClient but you can utilize oracle's ODP with using UDTs. If that is not an option, I am unsure how you can do it via parameters in C# with System.Data.

ODP附带了很多示例，并且上面的链接中有一些示例.

ODP does come with a lot of examples and there are examples in the above links.

我将添加更多希望对您有帮助的链接:

I am going to add some more links that will hopefully help:

1. Visual Studio ODP索引
2. 这确实向您展示了如何 利用ODT创建您的自定义 类包装器并调用它们( 请注意，这是中途， 他们逐步使用该工具 在其中创建自定义类型 示例-本演练是 相当彻底，应该可以帮助您 直接在您需要的位置)
3. 下载:现在，这家伙也 安装示例文件，这是 另一个很棒的例子 您需要做的:安装完成后 goto [您的目录路径 安装] .. \ product \ 11.2.0 \ client_1 \ odp.net \ samples \ 4 \ UDT \ object1.cs

1. visual studio ODP index
2. 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)
3. 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

允许Visual Studio的ODT工具为您的UDT创建类(例如IOracleCustomType等)确实值得.然后，您可以研究它们并对其进行修改以适合您的需求.然后一切都说完了(来自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):

    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);

还请注意，Person类必须实现 IOracleCustomType (可以通过按#2中的链接创建)

also note that Person class must implement IOracleCustomType (which can be created by following the link in #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

上面是针对完整的自定义类型的，但是您需要使用关联数组ODP绑定:

The above is for a full custom type, but you are after an associative array ODP binding:

http://weblogs. asp.net/ricardoperes/archive/2009/05/14/odp-net-associative-arrays.aspx

您将要使用

param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

一切都应该放到位

这篇关于Oracle存储过程和自定义数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！