My .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)
what am i doing wrong?
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 a 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 WHERE PARAMETER='NLS_NUMERIC_CHARACTERS';
The value for the Paremeter NLS_NUMERIC_CHARACTERS contains 2 characters. The 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
Thank you very much Jake !!
You were right the NLS_DATABASE_PARAMETERS was ".," and I changed it to ", ".
The application runs on a PC running windows in French. The regional format set to France and the decimal separator used in France is a comma(,) instead of dot(.).
The application is trying to insert the decimal value 4,7 to a column which is defined as number_value NUMERIC(6, 2)