howto return TYPE IS TABLE OF from Package into C#

Boosa · Post by **Boosa** » Thu 25 Mar 2010 02:32

Hello,

I have a serious problem with returning a "table" from a package to my C# program.
I declared the following TYPE in the package:

Code: Select all

TYPE RetValUldDouble IS TABLE OF CHCSDBSHM.I_ULD_PRED_DATA%ROWTYPE INDEX BY BINARY_INTEGER;

[/color]

Then the function:

Code: Select all

FUNCTION FN_I_GET_ALL_ULD_FROM_MV2(i_MvmNr IN VARCHAR2, i_SrtDt IN VARCHAR2) RETURN RetValUldDouble

[/color]

It's a table with about 40 columns.

Declaration in the main function:

Code: Select all

v_UldArr RetValUldDouble;

v_uldArr is filled in this function and is returned at the end of the function

The C# ReturnValue Parameter is declared:

Code: Select all

oraComm.Parameters.Add("o_RetVal", OracleDbType.Array).Direction = ParameterDirection.ReturnValue;

I tried it also with OracleDbType.Table but without success.

When calling ExecuteNonQuery i get the following error message:
ORA-03115 unsupported network datatype or representation

Any ideas how I can return this multi column "table" to the program?
I have no problems with cursors or Tables with single columns like:

Code: Select all

TYPE RetVal IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;

But with multi-column table like the above mentioned:

Code: Select all

TYPE RetValUldDouble IS TABLE OF CHCSDBSHM.I_ULD_PRED_DATA%ROWTYPE INDEX BY BINARY_INTEGER;

[/color]

We use dotConnect for Oracle Professional Edition 5.25.49.0; VS2005 and Oracle 10g

Many Thanks in advance

Shalex · Post by **Shalex** » Fri 26 Mar 2010 13:40

Please refer to this thread to see the example of using dotConnect for Oracle with associative arrays (INDEX BY BINARY_INTEGER).
The %ROWTYPE parameter type is supported neither by Oracle client software nor in our Direct Mode.

Boosa · Post by **Boosa** » Fri 26 Mar 2010 17:48

So there is no way to return a value from type %ROWTYPE.
Is there any way to return a multicolumn type or a two dimensional array or something like this?
Direct Mode is used.

Boosa · Post by **Boosa** » Sat 27 Mar 2010 02:54

Shalex,
my intention was to easily create a "table" in the Package which i nearly only need to display to the user and having the code outside the program.
Now I'm doing the logic in the program and let return all the rows via cursor from Oracle which I need for that.

It's not a big deal but maybe for future work you have an idea according to my last post.

Thanks

Shalex · Post by **Shalex** » Mon 29 Mar 2010 11:11

The RECORD parameters are not supported. You can work with the OracleTable object that contains a collection of OracleObjects in the OCI mode. The Direct mode has some limitations which are listed here.

Boosa · Post by **Boosa** » Mon 29 Mar 2010 20:54

Okay, thanks a lot for your quick answers.
I like your support.

I'm curious about the future versions of dotConnet