Oracle returns decimal for a certain NUMBER(4) column

Question

I am executing a query using ODP.NET. The query selects a lot of columns from a table, including several NUMBER(4) columns.

When executing the query against a local dev 10.2 instance, all NUMBER(4) columns are returned as instances of Int16. This is OK and expected.

When executing the query against another 11.2 instance, all but the last NUMBER(4) columns are also instances of Int16, but the last one is an instance of decimal, which is currently breaking my code. I can fix the issue in my application, but the arbitrariness of it is killing me. How could this be? Is this even possible? I mean, it's the same query, same table, all columns are of the same type, yet one of them gets a different C# type in the data reader. How could this happen?

EDIT -> I am running tests. The problem happens even if I SELECT only that single column from that single table, without JOINs or UNIONs or WHERE or anything. Tried dropping the column and recreating it; same problem. Tried creating a copy of the table with the same columns, problem keeps happening. Tried creating a copy of the table with less columns, problem stopped happening. Is it possible that this happens solely because of the quantity of columns / position of the misbehaving column within the table? (The table has 77 columns and the problematic column is the last column in the table).

Answer 1

Yes, we ran into the same issue - ODP.NET replaces output NUMBER declarations with OracleDecimal even for values with no decimal fractions.

It appears to me that if type is declared as just NUMBER or interpreted like that by oracle query, it becomes decimal even if when value does not have fraction. This is in contrast with NUMBER(N,0) definitions where ODP.NET actually figures out .NET type based on number of digits and chooses smallest signed integer or floating point / decimal type to represent it.

If you provided your actual code example it would help, but for any ExecuteDataReader() calls the following code should be safe:

  using(var reader = cmd.ExecuteReader())
  {
    while(reader.Read())
    {
      var v = Convert.ToInt16(reader["name"]);
    }
  }

Problem gets worse with output parameters, because OracleDecimal does not implement IConvertible and does not convert to Int32 or similar types with Convert.ToInt32() call (it fails with exception).

The problem is OracleDataReader.GetInt16() has implementation like this:

  int num2 = (int) this.m_pOpoMetValCtx->pColMetaVal[i].Scale;
  int num3 = (int) this.m_pOpoMetValCtx->pColMetaVal[i].Precision;
  if (num2 > 0 || num3 - num2 >= 5)
    throw new InvalidCastException();

so it may still fail, but at least you will know, that scale was positive for some unknown reason. This pattern is repeated in multiple places and in general all NUMBER types are internally represented by ODP.NET as OracleDecimal and then depending on scale and precision are mapped to Int16, Int32, Int64, Float, Double and Decimal

So for the query below

  SELECT CAST(1 AS NUMBER(3,0)) as c1, CAST(220 AS NUMBER) as c2 FROM DUAL

the following works:

  var v = Convert.ToInt16(reader["c2"]);

while the following fails:

  var v = reader.GetInt16(1); // InvalidCastException

Another workaround I found is when defining output or return parameters, use of OracleDbTypeEx helps.

For example if oracle output parameter is created as

  var p = new OracleParameter("name",OracleDbType.Int32, ParameterDirection.Output)

it is returned as OracleDecimal and causes problems described before, but when declared like this:

  var p = new OracleParameter("name",OracleDbType.Int32, ParameterDirection.Output);
  p.OracleDbCodeEx = OracleDbType.Int32;

It works fine and value type is actually .net type and is System.Int32.

Overall ODP.NET implementation is quite poor, including some choices on methods overloading. For another example OracleDataReader.GetOrdinal() is implemented as a loop over column names. Users of this library have be very careful and pay extra attention to functional testing.