Insert multiple rows of data from C# List of objects into a table in Oracle 12 database

Question

I want to insert multiple rows through a stored procedure calling it from C#.NET. I am using OracleDataAccess.dll unmanaged .net driver provided by Oracle for .NET and using VS 2015. My program is a console program that will read excel files (one by one) looking a specific worksheets in the excel and insert the data from the worksheet into a table in oracle.

I have researched and found people say that it can be done through XML, UDT and OracleBulkCopy but the code I have found does not work. I tried testing it by running it as an xml line of code. The screen dump of this error is provided at the very end.

I am looking for a solution on how to pass it from C# to Oracle to achieve the outcome of inserting these records in the Oracle table. I would appreciate if you can help me figure this out on the Oracle and C#.NET.

C#.NET side code below:

public class DSelect
{
  public string Installation { get; set; }
  public int Account { get; set; }
  public int BusinessNumber { get; set; }
  public long Sysd { get; set; }        
  public decimal LocX { get; set; }        
  public string Type { get; set; }
  public string Wattage { get; set; }
  public decimal ALen { get; set; }
  public int LWatt { get; set; }
  public string Tempr { get; set; }
  public DateTime CreatedDate { get; set; }       
}

List<DSelect> listDSelect = new List<DSelect>();

.... Reading an excel worksheet row in a loop and building my list. There can be between 500 to 1000 rows of data in the worksheet

var deSel= new DSelect();
deSel.Installation  = "install1";
.................................
listDSelect.Add(deSel)

XmlSerializer serializer = new XmlSerializer(typeof(List<DSelect>));

var stringwriter = new System.IO.StringWriter();

serializer.Serialize(stringwriter, listDSelect);

Pass to stored procedure to insert into table in Oracle database 12c.

I want to pass the entire list to the oracle stored procedure as a object.
In the oracle stored procedure receive it and and insert it into a table.

Test code that fails in SQL Developer :

DECLARE
  p_AdditionRequest varchar2(30000); -- CLOB;
  t_xmlType SYS.XMLTYPE;
BEGIN    
  p_AdditionRequest:= '<?xml version="1.0" encoding="utf-16"?>
<ArrayOfDetailedSelection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DetailedSelection>
    <Installation>645805</InstallationNumber>
    <AccountNumber>33170019251</AccountNumber>
  </DetailedSelection>
</ArrayOfDetailedSelection>';
  --t_xmlType := sys.xmltype.createxml(p_AdditionRequest); 

  SELECT InstallationNumber, AccountNumber (
  SELECT ExtractValue(column_value, '/DetailedSelection/InstallationNumber') InstallationNumber,
         ExtractValue(column_value, '/DetailedSelection/AccountNumber') AccountNumber         
  FROM TABLE(XMLSequence(XMLTYPE(p_AdditionRequest).EXTRACT('/ArrayOfDetailedSelection/DetailedSelection')))
       )
END;
/

Jonathan Willcock · Accepted Answer · 2019-12-16 10:15:57Z

1

Your SQL code is close, but you have a couple of errors. Firstly, you are inconsistent between Installation and InstallationNumber and secondly you are missing an INTO. I created a test table to hold the data, but something like this should work:

DECLARE
  p_AdditionRequest varchar2(30000); -- CLOB;
BEGIN    
  p_AdditionRequest:= '<?xml version="1.0" encoding="utf-16"?>
<ArrayOfDetailedSelection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DetailedSelection>
    <InstallationNumber>645805</InstallationNumber>
    <AccountNumber>33170019251</AccountNumber>
  </DetailedSelection>
</ArrayOfDetailedSelection>';

INSERT INTO TESTDETAILEDSECTION
  SELECT InstallationNumber, AccountNumber
  FROM (
  SELECT ExtractValue(column_value, '/DetailedSelection/InstallationNumber') InstallationNumber,
         ExtractValue(column_value, '/DetailedSelection/AccountNumber') AccountNumber         
  FROM TABLE(XMLSequence(XMLTYPE(p_AdditionRequest).EXTRACT('/ArrayOfDetailedSelection/DetailedSelection')))
       );
END;
/

However I prefer using slightly different syntax for the SELECT, thus:

INSERT INTO TESTDETAILEDSECTION
  SELECT xt.INSTALLATION, xt.ACCOUNTNUMBER FROM 
XMLTABLE('/ArrayOfDetailedSelection/DetailedSelection' PASSING xmlType(p_AdditionRequest)
  COLUMNS 
  "INSTALLATION" varchar(20) PATH 'InstallationNumber',
  "ACCOUNTNUMBER" varchar(20) PATH 'AccountNumber') xt;

The advantage of this latter is that you can set the datatype of the fields in the xml when parsing. (I assumed that both InstallationNumber and AccountNumber were varchars). Also your suspicion is correct; when passing from c# you will need to use a clob. A little tip here, from c# you will need to set both OracleDbType and OracleDbTypeEx for your parameter to OracleDbTyp.Clob.

answered Dec 16, 2019 at 10:15

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

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

4 Comments

Ruruboy Over a year ago

Jonathan Thank you for your kind response. I like your idea of using XMLTable. If I pass in a longer XML string. Say there are 500 more of the "DetailedSelection" sections in the XML string then, it throws an error "can bind a LONG value for insert into a LONG column" even though i increased the size of datatype to varchar(100).

Ruruboy Over a year ago

Sorry Jonathan correction to my previous comment: I changed the type to DECLARE p_AdditionRequest CLOB; INSERT INTO TESTDETAILEDSELECTION SELECT xt.INSTALLATION, xt.ACCOUNTNUMBER FROM XMLTABLE('/ArrayOfDetailedSelection/DetailedSelection' PASSING to_clob(p_AdditionRequest) COLUMNS "INSTALLATION" varchar(100) PATH 'InstallationNumber', "ACCOUNTNUMBER" varchar(100) PATH 'AccountNumber') xt; using t_clob() function it compiled well, but when I ran it nothing was inserted into the table.

Jonathan Willcock Over a year ago

Don't use to_clob(). Declare your xml as clob but you must still use PASSING xmltype(p_AdditionRequest). If this doesn't work, please Email me the xml that is not working and I'll look at it agin in the morning. It's getting late here (21.40 German time). My email is [email protected]

Ruruboy Over a year ago

Jonathan thank you so much. I will try it out and will email you if it is not working. Good night.

Collectives™ on Stack Overflow

Insert multiple rows of data from C# List of objects into a table in Oracle 12 database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related