0

I have and xml message that contains an array of string data, smth like this : 

<DataList>
  <Data>some data no 1</Data>
  <Data>some data no 2</Data>
</DataList>

I store it in my 2 classes: 

[XmlElement("DataList")]
public DataList DataList { get; set; }

and for the array:

public struct DataList
 {
   [XmlElement]
   public string Data { get; set; }
 }

So , i need to pass this array as an input parmater to my oracle stored procedure

How am I suppose to do that, both on oracle and c# sides? (by creating a table\refcursor in oracle and passing them as a ref cursor? or by using for loop?) 

parameter.Add("Data", OracleDbType.-don't know-).Value = DataList.Data; or just DataList?
Eve
  • 101
  • 2
  • 13
  • See the linked answer - just use `CREATE OR REPLACE TYPE stringarray AS TABLE OF CLOB;` (or you can try `TABLE OF XMLTYPE` OR `TABLE OF VARCHAR2(4000)` if the xml will be small enough) and then wrap it in `XMLTYPE()` at the other end. – MT0 Apr 06 '17 at 09:37
  • Oops... a better duplicate would have been [Calling Stored procedures from C# with Oracle type like Table of CustomType](http://stackoverflow.com/q/21305028/1509264) or [Calling oracle package function with table of varchar2(100) index by binary_integer datatype as a parameter from c# .net](http://stackoverflow.com/q/21305028/1509264) – MT0 Apr 06 '17 at 09:40
  • 1st variant: c# parameter.Add("Data", OracleDbType.XmlType).Value = DataList; , in oracle: type data_array IS TABLE OF XMLTYPE; - i'm getting the c# error: Unsupported column datatype. – Eve Apr 06 '17 at 10:25
  • 2nd try: c#parameter.Add("Data", OracleDbType.Varchar2).Value = DataList; oracle - type data_array IS TABLE OF Varchar2; getting the oracle error - wrong number or types of arguments in call – Eve Apr 06 '17 at 10:27
  • Pass it as a `TABLE OF CLOB` or `TABLE OF VARCHAR2(4000)` (depending on the maximum size of your XML data) - you may also need to use `INDEX BY BINARY_INTEGER`. See http://stackoverflow.com/a/21306124/1509264. – MT0 Apr 06 '17 at 10:31
  • Also see http://stackoverflow.com/q/29605713/1509264. You need to make sure you set `parameter.CollectionType` to the appropriate type (i.e. `OracleCollectionType.PLSQLAssociativeArray` if you are using `TABLE OF X INDEX BY BINARY_INTEGER`). – MT0 Apr 06 '17 at 10:35
  • i've already tried to pass it a table of varchar2, added index by, didn't make any difference. I understood the part of oracle. The question is how to pass the parameters via c#. OracleDbType.Varchar2 or XMLType both not working in here. – Eve Apr 06 '17 at 10:39
  • I've reopened the question so you can, hopefully, get some more help (although I still believe the question should be answered by one of the questions I have already linked). Please edit your question and post a [MCVE] of your attempt and the full error messages. – MT0 Apr 06 '17 at 10:43

0 Answers0