linq2db.EntityFrameworkCore: Getting error "ORA-00928" when trying to context.BulkCopy

Hey!

I just discovered linq2db and linq2db.EntityFramwrokCore while searching for a Bulk Insert option that would work with EF Core 3.1 and Oracle. At first I was a bit skeptical of it being so simple to work with. Now I think I may be missing something, because it’s not working for me.

Ok, so I have a collection of <FlatConsult> entity that I need to upsert into my Oracle (19c) database. This is the code:

LinqToDBForEFTools.Initialize();
List<FlatConsult> data = GetData();
dwDb.BulkCopy(new BulkCopyOptions 
{ 
    BulkCopyType = OracleTools.DefaultBulkCopyType, 
    TableName = "ST.\"FlatConsults\"" 
}, data);

I had to specify the table name otherwise I would get a “ORA-00942: table or view does not exist” error. But now I keep getting a “ORA-00928: missing SELECT keyword” error:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00928: palavra-chave SELECT não encontrada
   em OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   em OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
   em Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   em LinqToDB.Data.DataConnection.ExecuteNonQuery(IDbCommand command) na D:\a\1\s\Source\LinqToDB\Data\DataConnection.cs:linha 1256
   em LinqToDB.Data.DataConnection.ExecuteNonQuery() na D:\a\1\s\Source\LinqToDB\Data\DataConnection.cs:linha 1261
   em LinqToDB.Data.CommandInfo.Execute() na D:\a\1\s\Source\LinqToDB\Data\CommandInfo.cs:linha 863
   em LinqToDB.DataProvider.MultipleRowsHelper.Execute() na D:\a\1\s\Source\LinqToDB\DataProvider\MultipleRowsHelper.cs:linha 100
   em LinqToDB.DataProvider.BasicBulkCopy.MultipleRowsCopyHelper(MultipleRowsHelper helper, IEnumerable source, String from, Action`1 prepFunction, Action`3 addFunction, Action`1 finishFunction, Int32 maxParameters, Int32 maxSqlLength) na D:\a\1\s\Source\LinqToDB\DataProvider\BasicBulkCopy.cs:linha 279
   em LinqToDB.DataProvider.Oracle.OracleBulkCopy.OracleMultipleRowsCopy1(MultipleRowsHelper helper, IEnumerable source) na D:\a\1\s\Source\LinqToDB\DataProvider\Oracle\OracleBulkCopy.cs:linha 186
   em LinqToDB.DataProvider.Oracle.OracleBulkCopy.MultipleRowsCopy[T](ITable`1 table, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\DataProvider\Oracle\OracleBulkCopy.cs:linha 126
   em LinqToDB.DataProvider.BasicBulkCopy.BulkCopy[T](BulkCopyType bulkCopyType, ITable`1 table, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\DataProvider\BasicBulkCopy.cs:linha 21
   em LinqToDB.DataProvider.Oracle.OracleDataProvider.BulkCopy[T](ITable`1 table, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\DataProvider\Oracle\OracleDataProvider.cs:linha 300
   em LinqToDB.Data.DataConnectionExtensions.BulkCopy[T](DataConnection dataConnection, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\Data\DataConnectionExtensions.cs:linha 2194
   em LinqToDB.EntityFrameworkCore.LinqToDBForEFTools.BulkCopy[T](DbContext context, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB.EntityFrameworkCore\LinqToDBForEFTools.ContextExtensions.cs:linha 32
   em Mantic.BlackOps.Jobs.Maintenance.Utils.FlatConsultsProcessor.<UpdateConsultsAsync>d__5.MoveNext() na C:\Repos\Tekap.Mantic.BlackOps\Mantic.BlackOps.Jobs.Maintenance\Utils\FlatConsultsProcessor.cs:linha 116
--- Fim do rastreamento de pilha do local anterior onde a exceção foi gerada ---
   em System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   em Mantic.BlackOps.Jobs.Maintenance.Jobs.FlatConsultsJob.<OrchestrateAsync>d__6.MoveNext() na C:\Repos\Tekap.Mantic.BlackOps\Mantic.BlackOps.Jobs.Maintenance\Jobs\FlatConsultsJob.cs:linha 79
[12:28:02 WRN T09] Failed to process the job '1': an exception occurred. Retry attempt 1 of 3 will be performed in 00:00:33. (Hangfire.AutomaticRetryAttribute)
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00928: palavra-chave SELECT não encontrada
   em OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   em OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
   em Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   em LinqToDB.Data.DataConnection.ExecuteNonQuery(IDbCommand command) na D:\a\1\s\Source\LinqToDB\Data\DataConnection.cs:linha 1256
   em LinqToDB.Data.DataConnection.ExecuteNonQuery() na D:\a\1\s\Source\LinqToDB\Data\DataConnection.cs:linha 1261
   em LinqToDB.Data.CommandInfo.Execute() na D:\a\1\s\Source\LinqToDB\Data\CommandInfo.cs:linha 863
   em LinqToDB.DataProvider.MultipleRowsHelper.Execute() na D:\a\1\s\Source\LinqToDB\DataProvider\MultipleRowsHelper.cs:linha 100
   em LinqToDB.DataProvider.BasicBulkCopy.MultipleRowsCopyHelper(MultipleRowsHelper helper, IEnumerable source, String from, Action`1 prepFunction, Action`3 addFunction, Action`1 finishFunction, Int32 maxParameters, Int32 maxSqlLength) na D:\a\1\s\Source\LinqToDB\DataProvider\BasicBulkCopy.cs:linha 279
   em LinqToDB.DataProvider.Oracle.OracleBulkCopy.OracleMultipleRowsCopy1(MultipleRowsHelper helper, IEnumerable source) na D:\a\1\s\Source\LinqToDB\DataProvider\Oracle\OracleBulkCopy.cs:linha 186
   em LinqToDB.DataProvider.Oracle.OracleBulkCopy.MultipleRowsCopy[T](ITable`1 table, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\DataProvider\Oracle\OracleBulkCopy.cs:linha 126
   em LinqToDB.DataProvider.BasicBulkCopy.BulkCopy[T](BulkCopyType bulkCopyType, ITable`1 table, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\DataProvider\BasicBulkCopy.cs:linha 21
   em LinqToDB.DataProvider.Oracle.OracleDataProvider.BulkCopy[T](ITable`1 table, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\DataProvider\Oracle\OracleDataProvider.cs:linha 300
   em LinqToDB.Data.DataConnectionExtensions.BulkCopy[T](DataConnection dataConnection, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB\Data\DataConnectionExtensions.cs:linha 2194
   em LinqToDB.EntityFrameworkCore.LinqToDBForEFTools.BulkCopy[T](DbContext context, BulkCopyOptions options, IEnumerable`1 source) na D:\a\1\s\Source\LinqToDB.EntityFrameworkCore\LinqToDBForEFTools.ContextExtensions.cs:linha 32
   em Mantic.BlackOps.Jobs.Maintenance.Utils.FlatConsultsProcessor.<UpdateConsultsAsync>d__5.MoveNext() na C:\Repos\Tekap.Mantic.BlackOps\Mantic.BlackOps.Jobs.Maintenance\Utils\FlatConsultsProcessor.cs:linha 116
--- Fim do rastreamento de pilha do local anterior onde a exceção foi gerada ---
   em System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   em Mantic.BlackOps.Jobs.Maintenance.Jobs.FlatConsultsJob.<OrchestrateAsync>d__6.MoveNext() na C:\Repos\Tekap.Mantic.BlackOps\Mantic.BlackOps.Jobs.Maintenance\Jobs\FlatConsultsJob.cs:linha 88
--- Fim do rastreamento de pilha do local anterior onde a exceção foi gerada ---
   em System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   em Mantic.BlackOps.Jobs.Maintenance.Jobs.FlatConsultsJob.<>c__DisplayClass5_0.<<Start>b__0>d.MoveNext() na C:\Repos\Tekap.Mantic.BlackOps\Mantic.BlackOps.Jobs.Maintenance\Jobs\FlatConsultsJob.cs:linha 41
--- Fim do rastreamento de pilha do local anterior onde a exceção foi gerada ---
   em System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   em System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   em Mantic.BlackOps.Jobs.Maintenance.Jobs.FlatConsultsJob.<Start>d__5.MoveNext() na C:\Repos\Tekap.Mantic.BlackOps\Mantic.BlackOps.Jobs.Maintenance\Jobs\FlatConsultsJob.cs:linha 40

I even added linq2db.Oracle.Managed to see if it would help, but no success. I’m probably missing some more configuration, but I couldn’t find any help on it.

And thank you for your work!

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 22 (13 by maintainers)

Most upvoted comments

It might solve this. It actually states in it’s description that it should be set to false. I’ll test it and report back shortly.

Edit: yep, that did it. Everything seems to be working as intended now. I don’t even have to define table or schema names in BulkCopyOptions anymore.

Thank you all for your time and support!

lhmiranda on Oct 7, 2020

Could you try and set

OracleTools.DontEscapeLowercaseIdentifiers = false;

option? I wonder why we have it set to true by default.

MaceWindu on Oct 7, 2020

I don’t have Oracle, but I found this link:

https://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF51129

Of particular interest: (added emphasis)

A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

So it would seem that we would need to support table names in the format of "name" in addition to name, and that it should not add additional escaping. Also noteworthy:

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and “at” signs (@).

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

Perhaps it should follow this pseudocode:

If matches regex ^\"[^\"]*\"$ then use as-is (quoted identifier)
If contains \" then throw exception
If matches regex [^a-zA-Z_$#] anywhere in the string then assume quoted identifier and add quotes
Otherwise assume unquoted identifier

As for the linq2db.EntityFrameworkCore library, we may need to investigate how EF Core normally escapes table names, and match that.

Sorry, I don’t have Oracle to do testing with.

Shane32 on Oct 6, 2020