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)

String concatenation (even if hidden in `String.Format`) should never be used to insert values in SQL. Always parameterise you queries. — Richard, Apr 10 '18 at 12:44
And, what is the SQL you are sending: copy from debugger into the question please. — Richard, Apr 10 '18 at 12:45
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. — MT0, Apr 10 '18 at 12:46
Try *anonymous block*: `var query = "begin " + string.Join("; ", mydict...) + " end;"` — Dmitry Bychenko, Apr 10 '18 at 12:47
If you have *very many* inserts, the "cleaner" way is a bulk copy. — Jeroen Mostert, Apr 10 '18 at 12:49

Dmitry Bychenko · Accepted Answer · 2018-04-11T12:31:04.317

3

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

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

edited Apr 11 '18 at 12:31

answered Apr 10 '18 at 12:49

Dmitry Bychenko

180,369
20
160
215

If the key and/or value was a string then this opens the query up to SQL Injection. – MT0 Apr 10 '18 at 12:55
1

@MT0: quite right, there're at least **3** reasons not doing via anonymous block generation but with bulk insert. I've only wanted to amend the code in the question – Dmitry Bychenko Apr 10 '18 at 13:05
i need this just for a small one-time-use internal script to move data to a test db, no real dangers :) – Doc Apr 10 '18 at 13:06

score 2 · Answer 2 · answered Apr 10 '18 at 13:21

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();
}

MT0 · Answer 3 · 2018-04-10T12:58:48.360

1

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:

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

edited Apr 10 '18 at 12:58

answered Apr 10 '18 at 12:52

MT0

143,790
11
59
117

In *general case* (a lot of data, not necessary ints, frequent calls) it the solution we should use – Dmitry Bychenko Apr 10 '18 at 13:06

Performing multiple inserts at once in oracle with ExecuteNonQuery

3 Answers3