在C#中映射包含关联数组的Oracle UDT [英] Mapping Oracle UDT containing associative array in C#

问题描述

我正在进行一个小型培训项目，该项目指导我创建一个包含关联数组的Oracle UDT.

I am working on a small training project which directed me to create an Oracle UDT that contains an associative array.

数组定义如下:

create or replace TYPE TRNG_AUTHORS_TAB AS
    TABLE OF VARCHAR2(50) ;

然后，UDT如下使用它:

The UDT then uses this as follows:

create or replace TYPE TRNG_BOOK_OBJ AUTHID DEFINER
    AS OBJECT
    (
        <SNIP normal variable mappings>
        AUTHORS TRNG_AUTHORS_TAB

    ) NOT FINAL;

现在，我将其映射到C#类.将TRNG_BOOK_OBJ映射为具有适当工厂的UDT.

Now I am mapping this to a C# class. The TRNG_BOOK_OBJ is mapped as a UDT with an appropriate Factory.

但是，我无法弄清楚如何将关联数组映射为类的一部分.我曾尝试使用List或string []，但这些方法不起作用.

I cannot, however, figure out how to map the associative array as part of the class. I have attempted using a List or string[], but these did not work.

我的下一个想法是为TRNG_AUTHORS_TAB创建一个C#UDT类，然后可以将其映射到装饰有OracleArracyMapping属性的类，但是鉴于字符串，我无法弄清楚如何创建关联数组的UDT.其中包含的列没有要映射的名称.

My next thought is to create a C# UDT class for the TRNG_AUTHORS_TAB, which I can then map to the class decorated with the OracleArracyMapping attribute, but I can't figure out how to create the associative array's UDT, given that the string column contained within it has no name to map to.

有人可以通过举例说明如何将关联数组映射到List或C#数组，或者如何将关联数组类型映射到C#UDT类的示例来帮助我解决此问题吗?

Can anyone help me resolve this issue, either by giving examples of how to map an associative array to a List or C# array, or how to map the associative array type to a C# UDT class?

推荐答案

[关联]数组的定义如下:

The [associative] array is defined as follows:

create or replace TYPE TRNG_AUTHORS_TAB AS
   TABLE OF VARCHAR2(50) ;

这不是关联数组数据类型(也称为按表索引")；它是一个集合数据类型，并且在SQL范围内定义.

That is not an associative array data-type (also known as an "index by table"); it is a collection data-type and is defined in the SQL scope.

关联数组仅在PL/SQL范围内可用，并且可以按以下方式定义:

Associative arrays are only available in the PL/SQL scope and can be defined like:

CREATE PACKAGE package_name AS
  TYPE STRING_MAP IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
END;
/

C#支持使用以下方式将关联数组传递给存储过程:

C# supports passing associative arrays to stored procedures using something like this:

OracleParameter parameter = new OracleParameter();
parameter.ParameterName  = "YourParameterName";
parameter.OracleDbType   = OracleDbType.Varchar2;
parameter.Direction      = ParameterDirection.Input;
parameter.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
parameter.Value          = new String[5]{"a","b","c","d","e"};

C#不不支持传递非关联数组.

C# does NOT support passing non-associative arrays.

如果要将集合(非关联数组)定义为用户定义类型(UDT)的成员，则必须使用传递关联数组然后使用PL/SQL的中间步骤在将其分配给UDT之前将其转换为一个集合.您可以将所有这些包装在PL/SQL包中的存储过程中.

If you want to have a collection (a non-associative array) defined as a member of a user-defined type (UDT) then you will have to use an intermediate step of passing an associative array and then using PL/SQL to convert it to a collection before assigning it to the UDT. You can wrap all this in a stored procedure in a PL/SQL package.

这篇关于在C#中映射包含关联数组的Oracle UDT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！