Home » SQL & PL/SQL » SQL & PL/SQL » number format issue (oracle10g)
number format issue [message #430262] Mon, 09 November 2009 17:00 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Hello,

This may be very silly question. Is this(to_number(no,'999999')) wrong format?

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table yy(no number(8,2));

Table created.

SQL> insert into yy values(12.2);

1 row created.

SQL> select to_number(no,'9999.99') from yy;

TO_NUMBER(NO,'9999.99')
-----------------------
                   12.2

SQL> select to_number(no,'999999') from yy;
select to_number(no,'999999') from yy
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> 
Re: number format issue [message #430263 is a reply to message #430262] Mon, 09 November 2009 17:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions191.htm#i79512

TO_NUMBER converts expr to a value of NUMBER datatype. The expr can be a BINARY_FLOAT or BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt.

Please NOTE that nowhere does it say TO_NUMBER() can/should be used with datatype NUMBER.

Why are you abusing/misusing TO_NUMBER?
If anything, you should be using TO_CHAR()

[Updated on: Mon, 09 November 2009 17:09]

Report message to a moderator

Re: number format issue [message #430267 is a reply to message #430262] Mon, 09 November 2009 18:54 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Thank you!!!
Previous Topic: Using "with as" in a select statment.
Next Topic: add column dynamically
Goto Forum:
  


Current Time: Tue Feb 11 19:14:57 CST 2025