Oracle查询ATAN函数报错‘Specified cast is not valid’求助

Hey Joon, let's break down why you're hitting this error and how to fix it quickly!

What's Causing the Error? #

The root issue is a type mismatch between what your Oracle query returns and how you're trying to read it in C#:

• Oracle's ATAN() function returns a floating-point type (either BINARY_FLOAT or BINARY_DOUBLE), a non-decimal numeric type.
• Your code uses reader.GetDecimal(0) to pull the value, but .NET can't directly cast a floating-point value from Oracle to a decimal type—hence the "Specified cast is not valid" message.

Solutions to Fix It #

Option 1: Convert the Value in SQL

Modify your query to explicitly cast the result to a numeric type compatible with decimal in C#. Use CAST() or TO_NUMBER():

SELECT CAST(SUM(ATAN((CASE WHEN (t0.CustomerID = 'Test') THEN 1 ELSE 1 END))) AS NUMBER(18,6)) value 
FROM Customers t0 
WHERE(t0.CustomerID = 'Test')

This ensures Oracle sends a numeric type that GetDecimal(0) can read without issues.

Option 2: Read as a Floating-Point Type in C#

Since ATAN() inherently returns a float, read it as a double (or float) first, then convert to decimal if you need it:

while (reader.Read()) {
    // Read as double first
    double floatValue = reader.GetDouble(0);
    // Convert to decimal if required
    decimal decimalValue = Convert.ToDecimal(floatValue);
}

Bonus: Simplify Your Query #

Quick note—your CASE statement is redundant right now: it returns 1 whether the condition is true or false. You can simplify the query to make it cleaner:

SELECT SUM(ATAN(1)) value FROM Customers t0 WHERE(t0.CustomerID = 'Test')

That should get your code running smoothly!

内容的提问来源于stack exchange，提问作者Joon w K