Performing multiple inserts at once in oracle with ExecuteNonQuery

Question

I'm trying to execute multiple inserts at once like this

var mydict =  new Dictionary<int, int> { { 1, 2 }, { 3, 4 } };

var query = string.Join("; ", mydict.Select(x => $"insert into myTable (colA, colB) values ({x.Key},{x.Value})"));

using(var connection = new new OracleConnection(dbConnectionString))
{
    var command = connection.CreateCommand();
    command.CommandText = query;
    command.ExecuteNonQuery();
}     

but i got Oracle.ManagedDataAccess.Client.OracleException: 'ORA-00911: invalid character' even I can manually execute the generated query from sqldeveloper with no issues.

I alreay did this in the past with sqlserver and sqlite, and i had no issues.

why this happens? is there a cleaner way?

---

here is the generated sql:

insert into myTable (colA, colB) values (72520,2452); insert into myTable (colA, colB) values (73293,2453)

Answer 1

In case of Oracle you should generate anonymous block, e.g.:

 begin -- wrap in begin .. end
   insert into myTable (colA, colB) values (72520, 2452); 
   insert into myTable (colA, colB) values (73293, 2453); -- do not forget last ;
 end;

In your case

var query = 
  "begin " + 
     string.Join("; ", mydict
       .Select(x => $"insert into myTable (colA, colB) values ({x.Key},{x.Value})")) + 
  "; end;";

Disclaimer: Do not do this (but implement bulk insert, see MT0 answer) if

1. You have to insert strings (SQL injection)
2. You have a lot of records to insert (bulk operations work faster)
3. You perform the call frequently (hard parsing, see https://blogs.oracle.com/sql/improve-sql-query-performance-by-using-bind-variables)

Answer 2

why this happens?

Oracle does not allow multiple statements to be executed in one command.

SQL developer will split your string into multiple statements and execute each in turn running them as multiple commands,

is there a cleaner way?

Use a batch/bulk insert:

• Bulk Insert to Oracle using .NET
• ORA-00604 error while batch insertion inside TransactionScope
• OracleBulkCopy does not insert entries to table

This way you can use bind values and are not building your insert statement as one huge string.

Answer 3

That is not the way you should do it. The preferred way would be like this:

var command = connection.CreateCommand();
command.CommandText = "insert into myTable (colA, colB) values (:ColA, :ColB)";
command.Parameters.Add("ColA", OracleDbType.Int64, ParameterDirection.Input);
command.Parameters.Add("ColB", OracleDbType.Int64, ParameterDirection.Input);

foreach ( var entry in mydict ) {
   command.Parameters["ColA"].Value = entry.Key;
   command.Parameters["ColA"].Value = entry.Value;
   command.ExecuteNonQuery();
}