How to pass table parameter from C # to Oracle stored procedure

Question

I have an Oracle stored procedure named CREATE_CASE_EXL :

 PROCEDURE CREATE_CASE_EXL(P_RICdata RICTab, P_sACTION_TYPE IN VARCHAR2); 

where RICTab is a custom type:

 TYPE RICTab IS TABLE OF MMSRRicRec INDEX BY BINARY_INTEGER; TYPE MMSRRicRec IS RECORD ( RIC VARCHAR2(32), FID_NO NUMBER(8), REC_ID NUMBER(8), MMS_ACTION VARCHAR2(1) ); 

I run this code in PL / SQL to execute CREATE_CASE_EXL :

 DECLARE pTE_RICS RICTab BEGIN pTE_RICS(1).RIC := 'RIC1'; pTE_RICS(1).FID_NO := NULL; pTE_RICS(1).REC_ID := 3; pTE_RICS(1).MMS_ACTION := 'A'; pTE_RICS(1).RIC := 'RIC2'; pTE_RICS(1).FID_NO := NULL; pTE_RICS(1).REC_ID := 4; pTE_RICS(1).MMS_ACTION := 'A'; CREATE_CASE_EXL( pTE_RICS , 'A'); END; 

I need to do something similar in .NET. Can you tell me how to pass a parameter as a data table to an Oracle stored procedure? Should I use UDT for this task?

Answer 1

In fact, I found that it is supported, but for this you need to use Oracle UDT.

Here is a link to the resources that I used to solve the problem: http://appsjack.blogspot.co.uk/2010/09/pass-custom-udt-types-to-oracle-stored.html

Many thanks to all for the advice!

Answer 2

I do not think this is currently supported. I think you have to use some workaround.

1) XML

2) intelligent associative array

 pTE_RICS(1).RIC := 'RIC1'; -> pTE_RICS("item[0].RIC") := 'RIC1'; 

We chose between them a year ago, and we chose XML. (and also trying to get your business to work, but to no avail)