Julia asked this 2 weeks 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

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

Jake 2 weeks ago
What is the value that you are trying to insert?
Julia 2 weeks ago

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

Julia 2 weeks 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(.).