Re: Problem with TO_NUMBER function

paubert_at_mais.hydro.qc.ca (Philippe Aubert) writes:

> Environment : Oracle 7.0.15.4.0 under Solaris 2.3
> Sqlplus 3.1.2.3.1

> I got a problem with numbers lower than 1 :
> SQL> select to_number(.030,'90.99') from dual;
> ERROR:
> ORA-01722: Invalid Number

This same error happens on 7.0.16.6.0, again under Solaris 2.3, SQL*Plus 3.1.2.3.1

> SQL> select to_number(.030,'99.09') from dual;
 

> TO_NUMBER(.030,'99.00')
> -----------------------
> .03
 

> SQL> select to_number(1.030,'90.99') from dual;
 

> TO_NUMBER(1.030,'90.99')
> ------------------------
> 1.03
 

> It means Oracle cannot display a number lower than 1
> with a significant digit BEFORE the decimal point.

Yes, this also happens for To_Number(0.030,'90.99'), To_Number(-.030,'90.99'), and To_Number(-0.030,'90.99'). It can't be the implicit number->char conversion within the To_Number function, because, as you say, '99.99' always works fine. However, strangely enough, this works:

select to_number(to_char(.030,'90.99')) from dual;

> Is someone aware about this problem ? How could I avoid it
> without changing my programs because it worked with Oracle 6 ?
 

> Thanks to give me a quick answer.

Not sure if this will help, though.

> Philippe AUBERT
> address : paubert_at_mais.hydro.qc.ca

Hank Robinson
Oracle DBA
University of Warwick Received on Thu Aug 18 1994 - 15:34:02 CEST