SQL question

[Annie Keslassy]

select to_number('25000.00') from dual gives an error
invalid number
Why??
Thanks for helping

[Thomas Kyte]

In article <3BFE7408...@amig-group.com>, Annie says...

whats your NLS settings regarding this. Consider:

ops$tk...@ORA817DEV.US.ORACLE.COM> select to_number('25000.00') from dual
2
ops$tk...@ORA817DEV.US.ORACLE.COM> /

TO_NUMBER('25000.00')
---------------------
25000

ops$tk...@ORA817DEV.US.ORACLE.COM> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY

15 rows selected.

ops$tk...@ORA817DEV.US.ORACLE.COM> alter session set
NLS_NUMERIC_CHARACTERS=',.';

Session altered.

ops$tk...@ORA817DEV.US.ORACLE.COM> select to_number('25000.00') from dual;

select to_number('25000.00') from dual

*
ERROR at line 1:
ORA-01722: invalid number

ops$tk...@ORA817DEV.US.ORACLE.COM> c/./,/
1* select to_number('25000,00') from dual
ops$tk...@ORA817DEV.US.ORACLE.COM> /

TO_NUMBER('25000,00')
---------------------
25000

ops$tk...@ORA817DEV.US.ORACLE.COM>

Good chance that . should be a , in your database.

--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp

[Frank]

Yup - nls_language=French, I'd say

Regards, Frank