I was working on a customer's database (now 11.2, but it has been upgraded/migrated a handful of times since the 90's). As part of the current project, we are quering data for transformation into a new application schema. The query contains to_char(number_value) and for the oldest data, it would return the ORA-01722 error. As we added rownum filters to the where clause we managed to find a row that triggers it. The value was nominally 0.03 (there is another VARCHAR2 column that stores the string value the user typed into the application) The number value had about 40 decimal places

text_value=0.03
number_value=.030000000000000005828670879282071837224

Clearly those ending decimals are insignificant.

Observed an odd behavior in SQL*Plus 11.2 on a record that throws the ORA error, just querying number_value would not display the value at all, even with numwidth set to 40. A GUI tool used by the DBA would display it. I tried the dump function and it showed values, so I knew there were values physically in the db.

We corrected the data by applying a rounding using length(mod(number_value,1)) >20 (update <the table> set number_value = round(number_value, 20) where length(mod(number_value,1)) >=20). A quick way to scale the rounding to the magnitude. Then the to_char in the original query didn't throw the error anymore.

I've always known that Oracle stores numbers to 38 digits of precision. But it appeared that some were higher than that.

looking for some history from past Oracle versions regarding physical number storage (NUMBER with no scale or precision specified in column definition) to understand the nature of these particular records. I wasn't sure what to look at in the dump function to perhaps identify a storage issue (or at least something that would make to_char act up).

SQL> create table t ( x number );

Table created.

SQL>
SQL> insert into t values (.030000000000000005828670879282071837224 );

1 row created.

SQL>
SQL> col x format 999.999999999999999999999999999999999999999999999
SQL> set lines 160
SQL> col d format a80
SQL> select x, dump(x) d from t;

                                                 X D
-------------------------------------------------- --------------------------------------------------------------------
    .030000000000000005828670879282071837224000000 Typ=2 Len=21: 192,4,1,1,1,1,1,1,1,6,83,87,71,88,93,83,8,19,38,23,41

1 row selected.

SQL> select to_char(x) from t;

TO_CHAR(X)
----------------------------------------
.030000000000000005828670879282071837224

1 row selected.