how to define and retrieve Oracle output parameter table of UDT(object) in C#

Question

I am trying to figure out how to define and retrieve an Oracle OUTPUT parameter which is a table of UDT(object) in C#.

Specifically I am using C#, .NET 4.0 and Oracle.ManagedDataAccess

I cannot seem to find the right example for this specific problem - although I have found a variety of similar suggestions and solutions where the return type is a simple OracleDBType and not a table of UDT Object.

Assuming the following example:

create or replace TYPE  tsv 
as
object ( valuedate date, value binary_double, code number );

create or replace TYPE tsv_array 
as table of tsv;

create or replace procedure sp_example (p_values OUT tsv_array , p_id IN     Varchar2 )
is
  l_values tsv_array;
begin
...
select field1, field2, field3
   bulk collect
   into l_values
   from table(some_stuff);

   p_values := l_values;
end;

I thought based on some examples that OacleDbType.Object might be of use, but it doesn't seem to be a part of Oracle.ManagedDataAccess which is what I am using.

Can someone help enlighten me as to how I would;

1 - properly define the output parameter (e.g. new OracleParameter("p_values", OracleDbType.?,...)

2 - access / process the returned table of UDT of the output param

Your help is most appreciated !

score 0 · Answer 1 · answered Feb 02 '16 at 08:57

Managed version of ODAC doesn't support UDTs. You must use either unmanaged version or third party Oracle driver, such as Devart which has by the way much more intelligent support of UDTs where you don't need to create specific class for each UDT in advance.

how to define and retrieve Oracle output parameter table of UDT(object) in C#

1 Answers1