DataReader throws 'Specified cast not valid' when reading the result of dividing two long decimals in Oracle

Question

I've got the following example in VB (.NET 4.0, VS2010, Oracle 10g)

Dim objDb As New OracleOdp("my_connection_info.udl")
        Dim sql = <sql>
            SELECT 'okay' As PoC, TO_NUMBER('47,3222404188609999999999999999999999') / TO_NUMBER('21,3222404188609999') FROM dual
        </sql>.Value
        objDb.SQL = sql
        Dim dr = objDb.Execute()
        dr.Read()
        Console.WriteLine(String.Format("Field count: {0}", dr.FieldCount))
        Console.WriteLine(String.Format("First value (= 'okay'): {0}", dr(0)))
        Console.WriteLine(String.Format("Second value (throws cast error): {0}", dr(1)))

OracleOdp being a custom class to create a simple Oracle connection.

Upon executing this sample, I get an error at the last line as I try to read what's in the second column of my DataReader, saying "Specified cast is not valid" with the following stack trace (Source: Oracle.DataAccess):

   à Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
   à Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)
   à Oracle.DataAccess.Client.OracleDataReader.get_Item(Int32 i)
   à HelpPls.Module1.Main() dans C:\Users\xxxx\Documents\TempHelp\HelpPls\Module1.vb:ligne 24
   à System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   à System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   à System.Threading.ThreadHelper.ThreadStart()

I'm forced to use the Oracle.DataAccess DLL version 4.112.2.0.

The error happens every time that I try to divide a decimal with another, but I can't possibly truncate nor round the numbers as they're used elsewhere (not in VB, mind you!) in conditions.

How to make it work?

Answer 1

The way you would normally do this would be to use the OracleDecimal data type. I apologize I don't know VB.net, but in the spirit of demonstrating this, here is a simple example in C#:

OracleCommand cmd = new OracleCommand(<your query>, conn);

OracleDataReader reader = cmd.ExecuteReader();
reader.Read();

OracleDecimal d1 = reader.GetOracleDecimal(1);

From here, you could get the decimal value simply by:

decimal d2 = d1.Value;

or more succinctly, both statements in one:

decimal d = reader.GetOracleDecimal(1).Value;

All that said, the precision in the result is too much for a decimal, and this will throw an error. You really need a double precision to make this work. Any of these should work for you:

double d = reader.GetOracleDecimal(1).ToDouble();

or, better, as you won't lose precision

double d = reader.GetDouble(1);

which is pretty much what @jmcilhinney said in his comment.