How to pass a table-valued 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 execute something similar in .NET. Can you please advise how could I pass a parameter as a table of data to an Oracle stored procedure? Should I use a UDT for this task?

tranceporter, thank you for the link, but in the example they use single dimension array and in my case I need 2 dimension array which has 2 columns - RIC, FID_NO, REC_ID, MMS_ACTION. Cheers! — Evgeniy O
– Evgeniy O, Commented Oct 3, 2012 at 16:54

Evgeniy O · Accepted Answer · 2012-10-05 14:39:48Z

4

actually I found that it's supported, but you have to use Oracle UDT for that purpose.

Here's link to the resources which I used to solve the issue: http://appsjack.blogspot.co.uk/2010/09/pass-custom-udt-types-to-oracle-stored.html

Big thanks to everybody for the advices!

answered Oct 5, 2012 at 14:39

Evgeniy O

1431 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jakub.petr · Accepted Answer · 2012-10-03 17:56:35Z

0

I don't think this is currently supported. I think you will have to use some workaround.

1) XML

2) smart associative array

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

We were choosing between them a year ago and we chose XML. (and also trying to get your case working but without success)

answered Oct 3, 2012 at 17:56

jakub.petr

3,0413 gold badges26 silver badges36 bronze badges

1 Comment

Evgeniy O Over a year ago

Hi, actually I found that it's supported, but you have to use Oracle UDT for that reason. Here's couple of links describing how to do this:

Collectives™ on Stack Overflow

How to pass a table-valued parameter from C# to Oracle stored procedure

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related