'"Specified cast is not valid" when populating DataTable from OracleDataAdapter.Fill()

I can't seem to find this question anywhere on Google (or StackOverflow), which really surprised me, so I'm putting it on here to help others in the same situation.

I have a SQL query which runs fine on Oracle Sql Developer, but when I run it through C# usingadapter.Fill(table) to get the results, I get Specified cast is not valid errors (System.InvalidCastException).

Here is cut-down version of the C# code:

var resultsTable = new DataTable();

using (var adapter = new OracleDataAdapter(cmd))
{
    var rows = adapter.Fill(resultsTable);  // exception thrown here, but sql runs fine on Sql Dev

    return resultsTable;
}

And here is a simplified version of the SQL:

SELECT acct_no, market_value/mv_total
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0

If I remove the division clause, it doesn't error - so it's specific to that. However, both market_value and mv_total are of type Number(19,4) and I can see that the Oracle adapter is expecting a decimal, so what cast is taking place? Why does it work on SqlDev but not in C#?

c++sharporaclecastingdatatabledecimal


Solution 1:[1]

I know this thread is really old.. However I had a similar issue.

The best solution I have to use the Oracle method TO_BINARY_DOUBLE on Oracle Decimal column

Solution 2:[2]

I know this has been answered already, but I also found another alternative that I use as well. I used a CAST on the field that was giving me troubles.

Based on OP's SELECT command:

SELECT acct_no, CAST((market_value/mv_total) AS DECIMAL(14,4))  -- CAST to decimal here
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0

Solution 3:[3]

A more convenient method to solve this issue is to use SuppressGetDecimalInvalidCastException in the OracleDataAdapter:

var table = new DataTable();
await using var connection = new OracleConnection(connectionString);
var command = new OracleCommand(queryString, connection)
{
    CommandTimeout = commandTimeout
};

using var adapter = new OracleDataAdapter(command) { SuppressGetDecimalInvalidCastException = true };
await Task.Run(() => adapter.Fill(table));

From the documentation:

This property specifies whether to suppress the InvalidCastException and return a rounded-off 28 precision value if the Oracle NUMBER value has more than 28 precision.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 AAGF
Solution 2 FrankTheTank
Solution 3 leon22

Related Questions