Home » SQL & PL/SQL » SQL & PL/SQL » USING TO_NUMBER and GETTING INVALID NUMBER ERROR
USING TO_NUMBER and GETTING INVALID NUMBER ERROR [message #283321] Mon, 26 November 2007 13:17 Go to next message
jtc103
Messages: 19
Registered: May 2007
Junior Member
I am using 10g and for some reason, I cannot seem to use TO_NUMBER. I get an invalid number error. A simple query like:

select to_number('0.13') from dual;

fails.

Any input? Is this an installation problem or is there a way to get around this?
Re: USING TO_NUMBER and GETTING INVALID NUMBER ERROR [message #283322 is a reply to message #283321] Mon, 26 November 2007 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member

SQL> select to_number('0.13') from dual;

TO_NUMBER('0.13')
-----------------
              .13

SQL> 

It works for me!
Re: USING TO_NUMBER and GETTING INVALID NUMBER ERROR [message #283323 is a reply to message #283321] Mon, 26 November 2007 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check your territory and nls numeric characters.
V$NLS_PARAMETERS

Regards
Michel
Re: USING TO_NUMBER and GETTING INVALID NUMBER ERROR [message #283341 is a reply to message #283323] Mon, 26 November 2007 15:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, changing '0.13' to '0,13' might help. Here is a short illustration of Michel's suggestion (which you still need to investigate):
SQL> alter session set nls_numeric_characters = ',.';

Session altered.

SQL> select to_number('0.13') from dual;   --> your current situation
select to_number('0.13') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> select to_number('0,13') from dual;   

TO_NUMBER('0,13')
-----------------
              ,13

SQL>
Re: USING TO_NUMBER and GETTING INVALID NUMBER ERROR [message #283385 is a reply to message #283341] Tue, 27 November 2007 00:23 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Or explicitly declare your decimal and thousands separator:
SQL> select to_number('0,13') from dual;
select to_number('0,13') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> select to_number('0,13', '99999D99', 'nls_numeric_characters='',.''') from dual;

TO_NUMBER('0,13','99999D99','NLS_NUMERIC_CHARACTERS='',.''')
------------------------------------------------------------
                                                         .13


[Edit: typo]

[Updated on: Tue, 27 November 2007 00:23]

Report message to a moderator

Previous Topic: fetch out of sequence
Next Topic: order by in Minus Operator
Goto Forum:
  


Current Time: Sat Dec 10 16:33:35 CST 2016

Total time taken to generate the page: 0.14210 seconds