Home » SQL & PL/SQL » SQL & PL/SQL » to_number-ora - 01722: invalid number (Oracle 10.2.0, Microsoft Windows XP SP2)  () 1 Vote
to_number-ora - 01722: invalid number [message #398129] Wed, 15 April 2009 05:44 Go to next message
bgmail
Messages: 2
Registered: April 2009
Junior Member
Hi,

When I try to do this:

select to_number(col6) from test_col6; (please, see below)

-----
CREATE TABLE test_col6
(

COL6 VARCHAR2(17)
);

insert into test_col6 values ('-0000000400000.00');

select to_number(col6) from test_col6;
--------------------------------
I get ora - 01722: invalid number.

I don't see what is problem. Could somebody help me, please?

Thanks!
Re: to_number-ora - 01722: invalid number [message #398133 is a reply to message #398129] Wed, 15 April 2009 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Decimal point character is suspicious.
Re: to_number-ora - 01722: invalid number [message #398141 is a reply to message #398129] Wed, 15 April 2009 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE test_col6
  2  (
  3  COL6 VARCHAR2(17) );

Table created.

SQL> insert into test_col6 values ('-0000000400000.00');

1 row created.

SQL> select to_number(col6) from test_col6; 
TO_NUMBER(COL6)
---------------
        -400000

1 row selected.

SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';

Session altered.

SQL> select to_number(col6) from test_col6; 
select to_number(col6) from test_col6
       *
ERROR at line 1:
ORA-01722: invalid number

SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';

Session altered.

SQL> select to_number(col6) from test_col6; 
TO_NUMBER(COL6)
---------------
        -400000

1 row selected.

Regards
Michel

[Updated on: Wed, 15 April 2009 06:08]

Report message to a moderator

Re: to_number-ora - 01722: invalid number [message #398147 is a reply to message #398141] Wed, 15 April 2009 06:30 Go to previous message
bgmail
Messages: 2
Registered: April 2009
Junior Member
Thanks, thanks, thanks!!!!

It works Smile


Bilja
Previous Topic: View Related Help
Next Topic: Sorting in Merge
Goto Forum:
  


Current Time: Sun Dec 04 00:50:03 CST 2016

Total time taken to generate the page: 0.09441 seconds