Fixing Oracle.ManagedDataAccess Parameter Problems in UPDATE Queries
When executing an Oracle UPDATE, INSERT, or DELETE command with Oracle.ManagedDataAccess, parameter-related errors can occur even when the SQL statement appears correct.
A typical implementation may look like this:
public static int ExecuteNonQuery(
string sql,
Dictionary<string, object> parameters = null)
{
int affectedRowsCount;
OracleConnection connection = new OracleConnection();
OracleCommand oracleSqlCommand = null;
try
{
connection.Connect();
oracleSqlCommand = NewCommand(sql, connection);
if (parameters != null)
{
foreach (var param in parameters)
{
oracleSqlCommand.Parameters.Add(
new OracleParameter(param.Key, param.Value));
}
}
affectedRowsCount = oracleSqlCommand.ExecuteNonQuery();
}
finally
{
connection.Disconnect();
}
return affectedRowsCount;
}
This code may work for some values but fail when a parameter is null, when Oracle infers the wrong data type, or when parameter names do not match the SQL statement.
1. Enable BindByName
Oracle commands may bind parameters by their position unless BindByName is enabled.
Consider the following SQL:
UPDATE EMPLOYEES
SET NAME = :name,
SALARY = :salary
WHERE ID = :id
The parameters should be matched by name, not by the order in which they are added.
public static OracleCommand NewCommand(
string sql,
Oracle.ManagedDataAccess.Client.OracleConnection connection)
{
return new OracleCommand(sql, connection)
{
BindByName = true
};
}
With BindByName = true, the parameter named id is assigned to :id even if it is added before the other parameters.
2. Use DBNull.Value Instead of null
A C# null value is not automatically equivalent to an Oracle database NULL in every parameter scenario.
Use DBNull.Value when sending a null value to Oracle:
object value = param.Value ?? DBNull.Value;
Without this conversion, Oracle may fail to determine the parameter type or report that a parameter is missing.
3. Specify OracleDbType Explicitly
This code relies on automatic type inference:
new OracleParameter(param.Key, param.Value)
Type inference can cause problems, especially with:
nullvaluesDateTimedecimallonglarge strings
CLOBvaluesnumeric values represented as strings
For reliable database code, define the Oracle type explicitly:
command.Parameters.Add("name", OracleDbType.Varchar2).Value =
employeeName ?? (object)DBNull.Value;
command.Parameters.Add("salary", OracleDbType.Decimal).Value = salary;
command.Parameters.Add("id", OracleDbType.Int32).Value = employeeId;
This prevents Oracle from interpreting a value using an incorrect database type.
4. Keep Parameter Names Consistent
If the SQL contains the following placeholder:
WHERE ID = :employeeId
Use the same logical name when adding the parameter:
command.Parameters.Add(
"employeeId",
OracleDbType.Int32
).Value = employeeId;
I prefer storing parameter names without the colon:
"employeeId"
rather than:
":employeeId"
The colon belongs to the SQL placeholder. Keeping it out of the parameter collection also makes parameter normalization easier.
A Safer ExecuteNonQuery Implementation
The following version handles resources and null values more safely:
public static int ExecuteNonQuery(
string sql,
IDictionary<string, object> parameters = null)
{
using var connection =
new Oracle.ManagedDataAccess.Client.OracleConnection(
OracleConnection.ConnectionString);
using var command = new OracleCommand(sql, connection)
{
BindByName = true
};
if (parameters != null)
{
foreach (var parameter in parameters)
{
string parameterName =
parameter.Key.TrimStart(':');
object parameterValue =
parameter.Value ?? DBNull.Value;
command.Parameters.Add(
new OracleParameter(parameterName, parameterValue));
}
}
connection.Open();
return command.ExecuteNonQuery();
}
This version improves the original implementation by:
disposing the connection and command with
usingremoving the unused
OracleDataReaderconverting C#
nulltoDBNull.Valuenormalizing parameter names
binding parameters by name
allowing exceptions to retain their original stack trace
Recommended Version With Explicit Types
For production code, passing only a dictionary of names and values is often insufficient because it does not preserve Oracle data types.
A small parameter model can solve this:
public sealed class DbParameterValue
{
public OracleDbType Type { get; init; }
public object Value { get; init; }
public int? Size { get; init; }
}
The execution method can then create strongly typed parameters:
public static int ExecuteNonQuery(
string sql,
IDictionary<string, DbParameterValue> parameters = null)
{
using var connection =
new Oracle.ManagedDataAccess.Client.OracleConnection(
OracleConnection.ConnectionString);
using var command = new OracleCommand(sql, connection)
{
BindByName = true
};
if (parameters != null)
{
foreach (var item in parameters)
{
string name = item.Key.TrimStart(':');
DbParameterValue definition = item.Value;
var parameter = new OracleParameter
{
ParameterName = name,
OracleDbType = definition.Type,
Value = definition.Value ?? DBNull.Value
};
if (definition.Size.HasValue)
{
parameter.Size = definition.Size.Value;
}
command.Parameters.Add(parameter);
}
}
connection.Open();
return command.ExecuteNonQuery();
}
Example usage:
const string sql = @"
UPDATE EMPLOYEES
SET NAME = :name,
SALARY = :salary,
UPDATED_AT = :updatedAt
WHERE ID = :id";
var parameters = new Dictionary<string, DbParameterValue>
{
["name"] = new DbParameterValue
{
Type = OracleDbType.Varchar2,
Size = 200,
Value = "John Doe"
},
["salary"] = new DbParameterValue
{
Type = OracleDbType.Decimal,
Value = 2500.50m
},
["updatedAt"] = new DbParameterValue
{
Type = OracleDbType.TimeStamp,
Value = DateTime.Now
},
["id"] = new DbParameterValue
{
Type = OracleDbType.Int32,
Value = 10
}
};
int affectedRows = ExecuteNonQuery(sql, parameters);
Problems in the Original Connection Wrapper
The original wrapper contains a finalizer:
~OracleConnection()
{
Disconnect();
connection.Dispose();
}
A finalizer is not a reliable way to close database connections. Garbage collection is nondeterministic, so the connection may remain allocated longer than expected.
The class should implement IDisposable, or the application should use the managed Oracle connection directly inside a using statement.
It is also risky to catch an exception in the constructor without rethrowing it:
catch (Exception ex)
{
message = "Database connection failed: " + ex.Message;
}
The application may continue with an invalid or null connection, causing a less meaningful exception later. If connection creation fails, allow the exception to propagate or wrap it with additional context:
catch (Exception ex)
{
throw new InvalidOperationException(
"The Oracle connection could not be created.",
ex);
}
Avoid Empty Catch Blocks
The following block has no practical effect:
catch (Exception ex)
{
throw;
}
It can simply be removed. If logging is required, log the exception and use throw; to preserve the original stack trace:
catch (Exception ex)
{
Log.Error(ex, "Oracle ExecuteNonQuery failed.");
throw;
}
Do not use throw ex;, because it resets the stack trace and makes debugging harder.
Transaction Example
When multiple commands must succeed or fail together, use a transaction:
using var connection =
new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
connection.Open();
using var transaction = connection.BeginTransaction();
try
{
using var command = new OracleCommand(sql, connection)
{
BindByName = true,
Transaction = transaction
};
command.Parameters.Add(
"name",
OracleDbType.Varchar2,
200
).Value = employeeName ?? (object)DBNull.Value;
command.Parameters.Add(
"id",
OracleDbType.Int32
).Value = employeeId;
int affectedRows = command.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
Conclusion
The most common causes of parameter problems with Oracle.ManagedDataAccess are:
Parameters being bound by position instead of name.
Passing C#
nullinstead ofDBNull.Value.Depending on automatic Oracle data-type inference.
Using inconsistent parameter names.
Not disposing connections and commands deterministically.
For simple queries, enabling BindByName and converting null values may be sufficient. For production applications, explicitly defining OracleDbType, parameter size, and transaction behavior provides a much more reliable solution.
