CODEWITHSUNDEEP
javasqloracle11gsqlplustoad
MHegde
MHegde

Reputation: 397

Oracle database column data displaying extra decimal value

I have a oracle database table with one column (data type - NUMBER).

The data stored in that column is 1.1,

But in Oracle SQL Developer it is showing as - 1.10000000000000008881784197001252323389 and when I connect DB using java (JDBC) also it is showing same.

When I use Toad or SLQPLUS(using command prompt) it is showing correct that is 1.1

enter image description here

Thanks in advance.

Upvotes: 1

Views: 1248

Answers (2)

Stephen C
Stephen C

Reputation: 718758

In fact, it is Oracle that is displaying the number accurately. That value is the closest 64-bit floating point value to the ideal value 1.1. What Toad and SQLPLUS are doing is rounding the actual stored value to the nearest N decimal digits.

I recommend that you read this:

UPDATE

I just noticed that what you are doing here is using floating point numbers to represent version numbers. This is a bad idea. Version numbers are really symbols ... and not numbers that you would perform arithmetic on. The best idea is to represent them as either character strings, or as pairs / tuples of integers that represent the major no, minor no, patch no, etc.

Treating your package version numbers as symbols will avoid the kind of problem you are having now ... which can be due to the inexact representation issue, or to rounding errors in floating point <-> string conversions.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191255

The data stored in the column is exactly what SQL Developer is showing you. It and JDBC are showing you the actual value, the other clients are not. SQL*Plus and Toad are rounding the number for display. By default SQL*Plus makes the number fit into 10 characters but you can override that with an explicit format mask:

create table t42 (package_version number);
insert into t42 values (1.10000000000000008881784197001252323389);

select * from t42;

PACKAGE_VERSION
---------------
            1.1

set numformat 9.99999999999999999999999999999999999999
select * from t42;

                          PACKAGE_VERSION
-----------------------------------------
 1.10000000000000008881784197001252323389

Or implicitly with set numwidth:

set numformat ""
set numwidth 40
select * from t42;

                         PACKAGE_VERSION
----------------------------------------
1.10000000000000008881784197001252323389

You can use numwidth to see the same display change in an SQL Developer worksheet too. The table browser view in your screenshot shows the whole value all the time.

So you need to find out how that number was generated in the first place, and possibly correct it. It looks like a floating-point calculation issue as StephenC suggests, but it isn't obvious why a package version number might have been generated like that for that row, rather than simply being set to exactly 1.1.

Upvotes: 2

Related Questions