1

The following query is executed to add a new column to an existing table:

ALTER TABLE TABLE1 ADD COLUMN1 NUMBER(10, 0) DEFAULT -1 NOT NULL.

Using DBForge to check the database, result in NUMBER (10, 0) column

An OracleConnection (of Devart dotConnect) is used and the NumberMapping property is set:

connection.NumberMappings.Add(OracleNumberType.Integer, 6, 10, typeof(int));
connection.NumberMappings.Add(OracleNumberType.Integer, 11, 19, typeof(long));

As soon as the next query "SELECT * FROM TABLE1" is executed, the DataTable contains 'COLUMN1' column with DataType decimal! It should be an integer according to the NumberMappings.

If the 'DEFAULT -1 NOT NULL' part is removed in the query, the 'COLUMN1' DataType is Int32.

I have checked the schema of the (Data)Table as well and noticed the NumericPrecision is set to 0 (zero). It should be 10.

DataTable schema ;
using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
    schema = reader.GetSchemaTable();
}

The issue only occurs in certain tables (not all). Does Oracle provide a way to modify or fix the NumericPrecision?

  • I know nothing about Devart dotConnect, but number(10) is too big for int32 (max value ~2e9) – edc65 Jun 28 '18 at 20:36
  • NUMBER(10) will show DATA_PRECISION as 10 when you check the dictionary files (eg USER_TAB_COLS). I don't see anything that needs to be fixed within Oracle. – Michael Kutz Jun 29 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.