Thanks for the question, Xiaohe.
Asked: August 16, 2017 - 10:17 am UTC
Last updated: September 01, 2017 - 4:46 am UTC
Version: 12.1.0.2.0
Viewed 1000+ times
You Asked
Hi Sir,
Why the following binary_float/binary_double columns are display using ### ? Why they are displayed using scientific notation ?
SQL> create table t (c1bf binary_float, c2bd binary_float, c3on number);
Table created.
SQL> insert into t values (1, 2, 3);
1 row created.
SQL> set numwidth 10
SQL> set numformat ""
SQL> select * from t;
C1BF C2BD C3ON
---------- ---------- ----------
1.0E+000 2.0E+000 3
SQL> set numwidth 3
SQL> set numformat ""
SQL> select * from t;
C1BF C2BD C3ON
---- ---- ----
### ### 3
SQL>
Thanks,
Xiaohe
and we said...
This design decision is not documented anywhere and may actually be a bug. We only document that conversion "conforms substantially" to IEEE754. However, I have not found any place in this standard that would mandate scientific notation. On the other hand, the conversion code explicitly adds the exponent part, which looks like a conscious design decision. The documentation for TO_CHAR(number) says that we use the shortest string, without mentioning any exceptions for binary floating point data types:
If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.
Hence, I am inclined to consider this behavior to be a bug. As a workaround, use the explicit calls to TO_CHAR(col,'TM')
Is this answer out of date? If it is, please let us know via a Comment