sqlkata / querybuilder

SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird
https://sqlkata.com
MIT License
3.06k stars 499 forks source link

Oracle Error ORA-00933 (SQL command not properly ended) on insert of many entities #662

Open NunzioCar opened 1 year ago

NunzioCar commented 1 year ago

Oracle: Oracle Database 21c Express Edition SQLKata: 3.2.3 .NET 6

C#:


var columns = new [] {"Name", "Price"};

var valuesCollection= new [] {
    new object[] { "A", 1000 },
    new object[] { "B", 2000 },
    new object[] { "C", 3000 },
};

var result = await _db
                 .Query("Products")
                 .InsertAsync(columns, valuesCollection, cancellationToken: cancellationToken);

Compiled SQL Query:

INSERT INTO "Products" ("Name", "Price") VALUES (?, ?), (?, ?), (?, ?)

Error:

ORA-00933 SQL command not properly ended

However if I downgrade to SQLKata version 3.0.0-beta the insert works properly and the compiled SQL Query is:

INSERT ALL 
    INTO "Products" ("Name", "Price") VALUES (?, ?) 
    INTO "Products" ("Name", "Price") VALUES (?, ?) 
    INTO "Products" ("Name", "Price") VALUES (?, ?) 
SELECT 1 FROM DUAL

and I noted that the field MultiInsertStartClause of OracleCompiler no longer exists.