Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

InvalidCastException when reading big numbers using GetValue(..)

The following issue was encountered when trying to read a large number stored in a NUMBER(32) column using the Oracle ADO.NET connection.

Test setup:

  • Create a new table with a NUMBER(32) column
  • Insert 2 values, the number 37 and the (large) number 45111111111111098559879744323584
  • Try to read the number using the ADO.NET connection by using GetValue(..).
    It works flawlessly for the first number, but throws an exception for the second number.

Here is the code to reproduce the complete issue

using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Data.Common;

DbConnection conn = new OracleConnection();
conn.ConnectionString = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=ORCLCDB)));User ID=etlbox;Password=etlboxpassword;";
conn.Open();

var createCmd = conn.CreateCommand();
createCmd.CommandText = @"CREATE TABLE TESTTABLE (  LARGENR NUMBER(32,0) )";
createCmd.ExecuteNonQuery();

var insertCmd1 = conn.CreateCommand();
insertCmd1.CommandText = @"INSERT INTO TESTTABLE VALUES (37)";
insertCmd1.ExecuteNonQuery();

var insertCmd2 = conn.CreateCommand();
insertCmd2.CommandText = @"INSERT INTO TESTTABLE VALUES ('45111111111111098559879744323584')";
insertCmd2.ExecuteNonQuery();

var readCmd= conn.CreateCommand();
readCmd.CommandText = @"SELECT LARGENR FROM TESTTABLE";

using (IDataReader reader = readCmd.ExecuteReader()) {
    while (reader.Read()) {
        //throws System.InvalidCastException for 2nd row, but shouldn't?!
        object firstCol = reader.GetValue(0);

        //this won't work:
        //object firstCol = reader.GetInt64(0);
        //this won't work as well 
        //object firstCol = reader.GetInt32(0);

        //this will work, but now everything is a string :(
        //object firstCol = reader.GetString(0);
        Console.WriteLine("Read value: " + firstCol.ToString());
    }
}

I would have expected that GetValue would automatically return an Int for the first record, and a string for the second record.

To me this looks like a bug in the implementation. But to be fair, other databases don't allow these large numbers to be stored in their columns, so I am not quite sure if this is really a bug, or the actual expected behavior.

This post has been answered by Alex Keh-Oracle on Dec 13 2023
Jump to Answer
Comments
Post Details
Added on Dec 1 2023
10 comments
204 views