Mapping Oracle UDT containing associative array in C#

Question

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

The array is defined as follows:

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

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;

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

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.

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.

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?

Possible duplicate of https://stackoverflow.com/questions/50485294/pass-integer-array-to-oracle-procedure-by-c-sharp/50485813#50485813 — MT0, Jun 17 '19 at 14:50
@MT0 Yeah, not quite. That has nothing to do with mapping the associative array to a UDT, it deals simply with passing an associative array as a parameter. — Jeff, Jun 17 '19 at 14:53
That's not an associative array; `TABLE OF ` is a collection. `TABLE OF INDEX BY ` is an associative array and is only supported within the PL/SQL scope. Unless something has changed very recently in C# you cannot pass an array to a collection as it isn't supported; you need to actually use a PL/SQL associative array. — MT0, Jun 17 '19 at 14:56

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

The [associative] array is defined as follows:
create or replace TYPE TRNG_AUTHORS_TAB AS
   TABLE OF VARCHAR2(50) ;

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.

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# 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# does NOT support passing non-associative arrays.

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.

Mapping Oracle UDT containing associative array in C#

1 Answers1

Linked