是否可以从ODP.NET调用具有嵌套表参数的Oracle存储过程? [英] Can an Oracle stored procedure that has a nested table parameter be called from ODP.NET?

问题描述

我有一个存储过程，它具有几个嵌套表参数.

I've got a stored procedure that has a couple parameters that are nested tables.

CREATE TYPE FOO_ARRAY AS TABLE OF NUMBER;
CREATE TYPE BAR_ARRAY AS TABLE OF INTEGER;

CREATE PROCEDURE Blah(
  iFoos IN FOO_ARRAY,
  iBars IN BAR_ARRAY,
  oResults OUT SOMECURSORTYPE
) AS
BEGIN
  OPEN oResults FOR
    SELECT *
    FROM SomeTable T
    JOIN TABLE(iFoos) foos ON foos.column_value = T.foo
    JOIN TABLE(iBars) bars ON bars.column_value = T.bar;    
END

使用ODP.NET(Oracle.DataAccess.dll)，是否可以调用此存储过程并将数组传递到这些参数中?我发现传递数组的唯一方法是，如果参数类型是关联数组(一种在SQL中无法访问的不同类型的集合).

Using ODP.NET (Oracle.DataAccess.dll), is there a way to call this stored procedure and pass arrays into these parameters? The only way I've found to pass arrays is if the parameter type is an associative array (a different type of collection that isn't accessible within SQL).

推荐答案

Oracle还提供了一个免费工具来生成自定义.NET类，该类映射到您的嵌套表类型:

Oracle also offers a free tool to generate a custom .NET class that maps to your nested table type:

下载免费的Oracle Studio开发工具"，打开Server Explorer，打开"User Defined Types"节点，找到您的用户定义类型，右键单击并选择"Generate Custom Class".

Download "Oracle Developer Tools for Visual Studio" (free), open Server Explorer, open "User Defined Types" node, find your user defined type, right click and choose "Generate Custom Class".

以下是一个逐步入门，可以帮助您全面了解UDT:

Here's a walkthrough to get you started with UDTs in general:

http://www. oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/dotnet/udt/udt_otn.htm

这篇关于是否可以从ODP.NET调用具有嵌套表参数的Oracle存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！