Re: SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 23 Nov 2001 08:34:02 -0800
Message-ID: <9tltpq025su@drn.newsguy.com>

In article <3BFE7408.54A393E4_at_amig-group.com>, Annie says...
>
>select to_number('25000.00') from dual gives an error
>invalid number
>Why??
>Thanks for helping

whats your NLS settings regarding this. Consider:

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

TO_NUMBER('25000.00')

ops$tkyte_at_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$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set NLS_NUMERIC_CHARACTERS=',.'; Session altered.

ops$tkyte_at_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$tkyte_at_ORA817DEV.US.ORACLE.COM> c/./,/ 1* select to_number('25000,00') from dual ops$tkyte_at_ORA817DEV.US.ORACLE.COM> /

TO_NUMBER('25000,00')

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

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

--
Thomas Kyte (tkyte@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

Received on Fri Nov 23 2001 - 10:34:02 CST