We have stumbled into what appears to be a similar bug as @user4111944 and @"Alex Keh - Product Manager-Oracle" discussed in ManagedDataAccess - reader bug when using '--' and ':' in SQL text .
We are using Oracle.ManagedDataAccess v. 12.1.2400 (latest NuGet package) and get an OracleException with the message
ORA-01008: not all variables bound
when we try to run the following code::
var l_connectionString = "some connection string";
var l_commandText = "UPDATE test_table SET col_description = '-- test', col_time = :param WHERE col_id = 42";
using (IDbConnection l_connection = new Oracle.ManagedDataAccess.Client.OracleConnection(l_connectionString))
{
l_connection.Open();
var l_command = l_connection.CreateCommand();
l_command.CommandText = l_commandText;
var l_parameter = l_command.CreateParameter();
l_parameter.ParameterName = "param";
l_parameter.Value = DateTime.Now;
l_command.Parameters.Add(l_parameter);
l_command.ExecuteNonQuery();
}
The code runs perfectly if omitting the '--' OR the part assigning a parameter value (i.e. omitting ', col_time = :param').
The stack trace for the OracleException is as follows:
at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone)
at 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, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
UPDATE 1:
I have now tested with older versions of Oracle.ManagedDataAccess.dll as well:
- ODAC 12c Release 4 (12.1.0.2.4 - Released October 5, 2015) -- fails with ORA-01008: not all variables bound
- ODAC 12c Release 3 (12.1.0.2.1 - Released December 23, 2014) -- fails with ORA-01008: not all variables bound
- ODAC 12c Release 2 (12.1.0.1.2 - Released December 20, 2013) -- succeeds!
Hence, changes in the Oracle Managed Driver introduced between December 20, 2013 and December 23, 2014 has caused this problem.
UPDATE 2:
I dug some more into the issue and after decompiling the December 2013 and the December 2014 versions, I found the following interesting piece of code in the implementation of the method ExecuteNonQuery() in the internal class OracleInternal.ServiceObjects.OracleCommandImpl. The code marked in red was added in the December 2014 version:
...
else if (commandType != CommandType.TableDirect)
{
OracleCommandImpl.TrimCommentsFromSQL(ref str);
this.m_sqlStatementType = OracleCommandImpl.GetSqlStatementType(str);
this.m_bHasReturningClause = OracleCommandImpl.HasReturningClause(str, this.m_sqlStatementType);
}
...
Taking the OracleCommandImpl.TrimCommentsFromSQL() method for a test ride easily reveal its weaknesses. It does not take into account that the sequence '--' may appear within a quoted string, treating it as a comment and removes the rest of the line...
Could you please look into this issue?
Best regards,
Bernt