OracleBulkCopy and NUMBER with Scale negative
FJGR65 opened this issue · 2 comments
(ODP NET 23.6.1 with .NET Framework 4.8) There is a bug in OracleBulkCopy.WriteToServer when the destination column is of type NUMBER with negative scale. For example:
"Round 100" NUMBER(5, -2)
In these cases, the following exception occurs:
`
{
"$id": "1",
"$type": "Oracle.ManagedDataAccess.Types.OracleTypeException, Oracle.ManagedDataAccess",
"Message": "ORA-13412: invalid scale parameter: 254
https://docs.oracle.com/error-help/db/ora-13412/",
"Number": 0.
"Source": "Oracle Data Provider for .NET, Managed Driver",
"Data": {
"$type": "System.Collections.ListDictionaryInternal, mscorlib"
},
"InnerException": null,
"StackTrace": " at Oracle.ManagedDataAccess.Client.OracleBulkCopy.WriteDataSourceToServer(IBulkCopyDataSource dataSource)
at ExcelToDatabaseWpf.Configuraciones.ConfiguracionHojaExcel.d__89.MoveNext()",
"HelpLink": null,
"HResult": -2146233087
}
`
As is the case with the Oracle ODBC driver, the parameter scale that is created internally is assigned the value 254 (not defined or something like that).
In ODBC the problem arises when you want to insert a NULL. The solution for this problem is to create an OdbcDataAdapter and set the RowUpdating property:
`
OdbcDataAdapter.RowUpdating += this.RowUpdating
Where:
private void RowUpdating(object sender, OdbcRowUpdatingEventArgs e)
{
foreach (OdbcParameter p in e.Command.Parameters)
if (p.Scale == 254 && (p.Value == DBNull.Value || p.Value == null))
p.Scale = 0;
}
`
However, for OracleBulkCopy this solution is not feasible.