Re: BUG #6217: to_char() gives incorrect output for very small float values

On Tue, Sep 20, 2011 at 8:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> However, it would
> be interesting to know what Oracle etc do with NaN and Infinity,
> assuming they even support such numbers.

Note that it looks like NUMBER cannot store either Infinity or NaN.
They can only occur in BINARY_FLOAT and BINARY_DOUBLE. From the docs:

> If a BINARY_FLOAT or BINARY_DOUBLE value is converted to CHAR or NCHAR, and the input is either infinity or NaN (not
anumber), then Oracle always returns the pound signs to replace the value. 

And testing shows:


SQL> select to_char(cast('NAN' as binary_float), 'FM9999.9999') from dual;

TO_CHAR(CA
----------
##########

SQL> select to_char(cast('-Inf' as binary_float), 'FM9999.9999') from dual;

TO_CHAR(CA
----------
##########

SQL> select to_char(cast('+Inf' as binary_float), 'FM9999.9999') from dual;

TO_CHAR(CA
----------
##########


--
greg