Re: Problem with TO_NUMBER function

Dirk Moebius (moebius_at_athene.informatik.uni-bonn.de)
> Philippe Aubert (paubert_at_mais.hydro.qc.ca) wrote:
> > Environment: Oracle 7.0.15.4.0 Solaris 2.3 SQL*Plus 3.1.2.3.1
> > I got a problem with numbers lower than 1:
> > select to_number(.030,'90.99') from dual; ==> ORA-01722: Invalid Number
> The format 90.99 is wrong. Try 09.99 instead.

Running "select to_number(.030,'90.99') from dual;" against:

  Oracle 6.0.32.3.2  SunOS 4.1.3         ==> .03
  Oracle 6.0.33.2.3  SunOS 4.1.3         ==> .03
  Oracle 6.0.36.7.1  SunOS 4.1.3, HP-UX  ==> .03
  Oracle 6.0.37.1.1  SunOS 4.1.3         ==> .03
  Oracle 7.0.15.4.0  SunOS 4.1.3, HP-UX  ==> ORA-01722: invalid number
  Oracle 7.0.16.4.0  Solaris 2.3, HP-UX  ==> ORA-01722: invalid number

"help conversion"	shows To_Number(CHAR).
"help format numbers"	shows 099 but not 990.
"help format numbers"	shows To_Char(EXPR, FMT).

I thought that was a bit odd at first since I have always used the simple form of "to_number('whatever')" and "to_char(whatever, '99,990.99')" as a means of displaying a zero when the values is less than zero. It appears to be a RDBMS difference between 6 and 7. (I did not get a chance to see if it was the same under DOS or VMS; nor did I get the chance to check on 7.1.3 or 6.0.30.) I vaguely recall there were several cases in the V6 to V7 change where there were no complaints under V6, but V7 would issue the erros (as it should). Every time I have ever used To_Number, it has been To_Number(EXPR) or with Trunc/Round/Etc (as in Round(To_Number(EXPR),N)).

Thanks, -phil- Received on Mon Aug 29 1994 - 02:09:42 CEST