Julia asked this 11 months ago

ORA-22062: invalid input string error while trying to insert data from .Net app

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?


Best Answer by Jake 11 months ago

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

Julia 11 months ago

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(.).


Julia 11 months ago

The application is trying to insert the decimal value 4,7 to a column which is defined as number_value NUMERIC(6, 2)

Jake 11 months ago
What is the value that you are trying to insert?