Number column in Oracle table storing 'hidden' decimal places

First ever question here, because searching for these terms is way too vague. I don't know how the data got in to the table (I think just by "insert into tomtest(test) values (280.1);" but can't be sure) but this example seems odd. Why does the straight select show 1 decimal place, but the to_char select shows 14 decimal places?

SQL> set linesize 100
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> describe tomtest
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TEST                                                           NUMBER

SQL> select * from tomtest;

      TEST
----------
     280.1

SQL> select * from tomtest where test = 280.1;

no rows selected

SQL> select to_char(test) from tomtest;

TO_CHAR(TEST)
----------------------------------------
280.10000000000002

SQL> SELECT data_type, data_length, data_precision, data_scale FROM USER_TAB_COLS WHERE TABLE_NAME = 'TOMTEST' AND COLUMN_NAME = 'TEST';

DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
NUMBER          22

Solution

The reason is the default numberformat in sql plus. in the database you have the value 280.10000000000002 and not 280.1

here is an example

SQL> select 128.000000000000000000006 as test from dual;

test
-------------------------
                      128

SQL> select 128.200000000000000000006 as test from dual;

test
-------------------------
                    128,2

you can change the number format and check again

SQL> SET NUMF 999999999.99999999999999999999999999999999
SQL> select 128.200000000000000000006 as test from dual;

test
-------------------------------------------
       128.20000000000000000000600000000000

SQL>

Update: This phenomenon can be explained by the fact that your data type is only defined as a number, without precise specification of precision. This means that you can save a decimal number, but do not give an exact specification of the decimal places.

Here is an example that shows the differences.

CREATE TABLE test_num(
n1 NUMBER,
n2 NUMBER(23,15));

INSERT INTO test_num VALUES(208.20000000000006,208.20000000000006);

SELECT t.*FROM test_num t;

Resut in PLSQL/Developer:

N      N2
---    ---
208,2  208,200000000000030

in plsql Developer you can display numbers as char. If you want to that activate a checkbox in the preferences: Tools -> Preferences -> SQL Window -> Number fields to_char

in your case, I would round a number value to a certain number of decimal places on insert/update or change your data type from number to number(p, s) (Where p is the precision and s is the scale.)