Oracle Number issue on 18c vs 19c

user_11895121 0
Viewed 223

Need a confirmation on this below behavior of NUMBER Datatype on both the Oracle versions(18c vs 19c),

In 18c,

select cast(0.003856214813393653 as number(20,18)) from dual;

--output

0.00385621481339365

In 19c,

select cast(0.003856214813393653 as number(20,18)) from dual;

--output

0.003856214813393653

Why does the truncation of last digit happen for 18c?

Is this an issue with version?

Plus 18c seems to to be unable to handle scale values more than 17.

oracle18c oracle19c numbers sqldatatypes oracle
2 Answers

This is related to Oracle / PLSQL developer tool setting issue. please try with the below options to resolve the same

Tools -> Preferences -> SQL Window -> Number fields to_char

user_122557760

This is at the whim of the client settings not the database. For example, I ran all of these on the same database

SQL Plus
========
SQL> select cast(0.003856214813393653 as number(20,18)) from dual;

CAST(0.003856214813393653ASNUMBER(20,18))
-----------------------------------------
                               .003856215

SQL Developer
=============
select cast(0.003856214813393653 as number(20,18)) from dual;

0.003856214813393653

SQLcl
======
SQL> select cast(0.003856214813393653 as number(20,18)) from dual;

CAST(0.003856214813393653ASNUMBER(20,18))
-----------------------------------------
                             .00385621481

The client tool decides on the precision to show

user_73672930
Related