howto return TYPE IS TABLE OF from Package into C#

howto return TYPE IS TABLE OF from Package into C#

Postby 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;


Then the function:
Code: Select all
FUNCTION FN_I_GET_ALL_ULD_FROM_MV2(i_MvmNr IN VARCHAR2, i_SrtDt IN VARCHAR2) RETURN RetValUldDouble


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;


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

Many Thanks in advance
Boosa
 
Posts: 10
Joined: Thu 25 Mar 2010 02:07

Postby 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.
Shalex
Devart Team
 
Posts: 7340
Joined: Thu 14 Aug 2008 12:44

Postby 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
 
Posts: 10
Joined: Thu 25 Mar 2010 02:07

Postby 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
Boosa
 
Posts: 10
Joined: Thu 25 Mar 2010 02:07

Postby 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.
Shalex
Devart Team
 
Posts: 7340
Joined: Thu 14 Aug 2008 12:44

Postby 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 :wink:
Boosa
 
Posts: 10
Joined: Thu 25 Mar 2010 02:07


Return to dotConnect for Oracle