OracleDataReader InvalidCastException: "Specified cast is not valid" on Decimal Types in .NET

Question

When using Oracle.ManagedDataAccess.Client.OracleDataReader and then calling reader.GetValue(i), we get the exception above.

This can be simulated using this query:

SELECT 1/3 as foo FROM DUAL

The problem arises when the oracle data is of Oracle type Decimal, and the precision in the data exceeds the number of decimal places in the .NET decimal. And the main issue is that reader.GetValue(i) does this, so you have no control over the fact that it throws this error internally.

I actually have a workaround, which I will post as an answer since I've hit this a number of times in the past, and the web does not have a good answer yet.

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

Wouldn't SELECT 1/3 as foo FROM DUAL result in 0? (Maybe Oracle automatically casts to a decimal or float?) — Igor
– Igor, Commented Jun 19, 2019 at 19:25
Couldn't you use another method... like reader.GetDecimal() or something like that? — douglas.kirschman
– douglas.kirschman, Commented Jun 19, 2019 at 19:42
@douglas.kirschman, GetDecimal fails as well, unfortunately. — Daniel Williams
– Daniel Williams, Commented Jun 21, 2019 at 20:25
@Igor, Oracle does cast these properly, so you get 0.33333... — Daniel Williams
– Daniel Williams, Commented Jun 21, 2019 at 20:25
@DanielWilliams Just encountered this issue myself. You mentioned in your question that you would post a workaround as an answer but never did. — Jeff
– Jeff, Commented Apr 7, 2021 at 17:31

Daniel Williams · Accepted Answer · 2021-04-10 15:31:59Z

I'm sorry this took so long to post @Jeff! The solution is to get the field type, and if it is decimal, then to call the oracle explicit method GetOracleDecimal()

if (reader.GetFieldType(i).Name.ToUpper() == "DECIMAL") { var x = reader as Oracle.ManagedDataAccess.Client.OracleDataReader; var dec = x.GetOracleDecimal(i); value = dec.ToString(); }

Every other field in the universe will simply work with reader.GetValue() It's what makes Oracle special :)

Except, what if you are using the reader[] operator?? There has to be an reader["namedColumn"] Decimal getter() function that does not require an index integer argument, when not using 'i' index, such as, x["namedColumn"].GetOracleDecimal(), right? But there isn't. (-:

AttributeError · Accepted Answer · 2023-09-27 13:30:19Z

For anyone who uses the reader[] operator to access column information, I've got a method that handles finding the column ordinal & then using Daniel's answer (https://stackoverflow.com/a/67036136/5617618) to fix the issue with casting to a decimal.

public string GetColumnValue( IDataReader reader, string columnName ) { int columnOrdinal = reader.GetOrdinal(columnName); if (reader.IsDBNull(columnOrdinal)) { return null; } if (reader.GetFieldType(columnOrdinal).Name.ToUpper() == "DECIMAL") { return (reader as OracleDataReader).GetOracleDecimal(columnOrdinal).ToString(); } return reader[columnName].ToString(); }

This method will get the result based on the column name you provide, and convert the value to a string. You can then use the Convert standard library to get the right type from there.

Collectives™ on Stack Overflow

OracleDataReader InvalidCastException: "Specified cast is not valid" on Decimal Types in .NET

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related