2

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?

Improve this question
5
  • Could it be your use of a comma (,) as the decimal mark? TO_NUMBER Function shows using a period (.) in the example. Commented Sep 29, 2016 at 22:35
  • I forgot to say it, but the request is not the problem. It works fine in PL/SQL Developer! Commented Sep 30, 2016 at 0:06
  • 1
    It appears that ADO.NET is trying to map the Oracle data type to a Decimal and the actual value doesn't represent a valid Decimal. Try getting the value as a Double specifically and see if that works. The data reader should have a GetDouble method or the like. Commented Sep 30, 2016 at 1:38
  • @TnTinMn -- I think that's a localization thing. When we get data from Europe, their commas and periods are transposed. It drives me batty Commented Sep 30, 2016 at 3:50
  • @Hambone, I understand that. My point was that the documentation I referenced, indicates an optional language code to define proper parsing; it implies that the default expects a period as the decimal mark. Commented Sep 30, 2016 at 4:02

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I'm getting back to work in a few hours, I'll keep you updated ASAP! Thank you very much for your answer in the meantime!
It finally works ! Thank you very much to you two ! :)
It works for me too. A few days without dealing with this. Thanks!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.