guruprakash asked this 3 years ago

ORA-22062: invalid input string [40,7] at Oracle.DataAccess.Types.OracleDecimal

.Net application raises below error message when trying to insert data into an Oracle table :

ORA-22062: invalid input string [40,7] at Oracle.DataAccess.Types.OracleDecimal..ctor(String numStr, String format) at Oracle.DataAccess.Client.OracleParameter.PreBind_Decimal() at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize, Boolean bIsFromEF, Boolean bIsSelectStmt) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) 

anette 3 years ago
1 like

The problem is you are trying to insert the decimal value 40,7 in Oracle table however the database doesn’t consider this as a valid input string most likely because you are using comma as the decimal separator and your database might be set to use different character such as a dot (.)

You can query the SYS table NLS_DATABASE_PARAMETERS to find out the localization setting used in your DB. The following SQL statement tells you what is the decimal separator setting used.

SELECT * FROM NLS_DATABASE_PARAMETERS WHER PARAMETER='NLS_NUMERIC_CHARACTERS';

The value for the Paremeter NLS_NUMERIC_CHARACTERS contains 2 characters. First one is the decimal separator and second is the group separator. For example if the Parameter value is ., it means that decimal separator is . and group separator is ,

Example: 12,345,23.08

 

To change the decimal separator character run the SQL statement

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ", ";

The above statement will set decimal separator as comma (,) and group separator as space. 

example: 12 345 23,08