3

I intended convert data type string to decimal in Oracle data base and i get this error:

ORA-01722: número no válido 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number

Code: 

TO_NUMBER('5.0') AS numero

CAST('5.0' AS NUMBER) AS numero
Andres Felipe Polo
  • 49
  • 1
  • 1
  • 4
  • Possible duplicate of [NLS\_NUMERIC\_CHARACTERS setting for decimal](https://stackoverflow.com/questions/24571355/nls-numeric-characters-setting-for-decimal) – Patrick Bacon Feb 04 '18 at 03:19

3 Answers3

1
select TO_NUMBER('5.0') from dual;

select NLS_NUMERIC_CHARACTERS
 from v$nls_parameters;

 --There must be '.' in value filed to run above query
Mohd Ahmad
  • 55
  • 6
  • 1
    While this code may answer the question, providing additional context regarding **how** and **why** it solves the problem would improve the answer's long-term value. – Alexander Feb 03 '18 at 15:15
0

The code you have posted works fine for me.

However, check you NLS/locale settings. If I change mine to use something other that '.' as the decimal separator then I get the same error.

So it would appear that it may be down to your locale settings. You can check with select * from v$nls_parameters to see what the nls_numeric_characters is set to.

ishando
  • 306
  • 3
  • 7
0

thanks for your answeres, it served as a guide to find the root of the problem. verifying the local configuration of nls_numeric_characters encontre ',.' Apparently the server configuration is different and therefore caused the error in the Query.

To give solution, replace '.' by ',' I assumed the configuration of nls_numeric_characters on the server is with ','.

select TO_NUMBER(REPLACE('5.0','.',',')) from dual;
Andres Felipe Polo
  • 49
  • 1
  • 1
  • 4
  • This link https://stackoverflow.com/questions/24571355/nls-numeric-characters-setting-for-decimal contains more information related to my solution – Andres Felipe Polo Feb 03 '18 at 18:36