Handle table of user-defined type, passed from Oracle, in C#

[Mikael Vehkajärvi]

Hello

I am running ASP.NET (C#) using Oracle (PL/SQL, provider ODP.NET
11.1.0.6.20) and I have a procedure which at the moment returns a table of
records. The code below demonstrates this.

TYPE R_OutData_tab IS RECORD ( ... );
TYPE OutData_tab IS TABLE OF R_OutData_tab INDEX BY BINARY_INTEGER;

PROCEDURE PROPERTY_GET (tOutData OUT <packagename>.OutData_tab);
Since .NET doesn't support Oracle records I'm looking into rewriting the
procedure so that it returns a table of a user defined type instead. The
code below demonstrates this.

create type person_type as object (name varchar2(30), address varchar2(60),
age varchar2(3));

TYPE person_table IS TABLE OF odp_obj1_sample_person_type;

PROCEDURE PERSONS_GET(out_persons OUT person_table);
I know how to handle a single user-defined type in .NET returned from a
Oralce procedure but what I need to do now is to receive a table of a user
defined type. Is this supported in .NET?

Regards // Mikael

[Frans Bouma [C# MVP]]

Mikael Vehkajärvi wrote:

Hello

I am running ASP.NET (C#) using Oracle (PL/SQL, provider ODP.NET
11.1.0.6.20) and I have a procedure which at the moment returns a table of
records. The code below demonstrates this.

TYPE R_OutData_tab IS RECORD ( ... );
TYPE OutData_tab IS TABLE OF R_OutData_tab INDEX BY BINARY_INTEGER;

PROCEDURE PROPERTY_GET (tOutData OUT <packagename>.OutData_tab);
Since .NET doesn't support Oracle records I'm looking into rewriting the
procedure so that it returns a table of a user defined type instead. The
code below demonstrates this.

create type person_type as object (name varchar2(30), address varchar2(60),
age varchar2(3));

TYPE person_table IS TABLE OF odp_obj1_sample_person_type;

PROCEDURE PERSONS_GET(out_persons OUT person_table);
I know how to handle a single user-defined type in .NET returned from a
Oralce procedure but what I need to do now is to receive a table of a user
defined type. Is this supported in .NET?

Why don't you return a cursor?

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------