Funny that oracle can easily cast ‘nan’,’inf’,’infinity’,’-inf’,’-infinity’ to corresponding binary_float_infinity,binary_double_nan, but there is no any format models for to_char(binary_float_infinity,format) or to_binary_***(text_expr,format) that can output the same as to_char(binary_float_infinity)/to_binary_float(‘inf’) without format parameter:
If a BINARY_FLOAT or BINARY_DOUBLE value is converted to CHAR or NCHAR, and the input is either infinity or NaN (not a number), then Oracle always returns the pound signs to replace the value.
Little example:
SQL> select to_binary_float('inf') from dual;
TO_BINARY_FLOAT('INF')
----------------------
Inf
SQL> select to_binary_float('inf','9999') from dual;
select to_binary_float('inf','9999') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select
2 to_char(binary_float_infinity) without_format
3 ,to_char(binary_float_infinity,'99999') with_format
4 ,to_char(1e6d,'99999') too_large
5 from dual;
WITHOUT_F WITH_FORMAT TOO_LARGE
--------- ------------------ ------------------
Inf ###### ######
SQL> select to_char(0/0f) without_format, to_char(0/0f,'tme') with_format from dual;
WITHOUT_F WITH_FORMAT
--------- --------------------------------------------------------------------------
Nan ################################################################
ps. it’s just crossposting of my old blog.