Number display issue in SQL Window

[jlathion]

Hello Marco,

Today, I noticed a display issue with some huge number values when querying a table in an SQL Window. It seems that some additional decimals are being added at the end of the number (the 6th decimal in my case)

I executed the following script on my database :

CREATE TABLE TEST_NUMBER (ID NUMBER, VAL NUMBER(22,7));
INSERT INTO TEST_NUMBER(ID,VAL) VALUES(1,123456789012.22);
INSERT INTO TEST_NUMBER(ID,VAL) VALUES(2,123456789012.1);
SELECT ID, VAL, (VAL-TRUNC(VAL)) AS DECIMAL_VAL FROM TEST_NUMBER;

In this case, the value displayed for column VAL in the select statement is 123456789012.2200010 for the first record and 123456789012.1000060 for the second record.

If I run the same query but using some other clients (SQL*Plus or Oracle SQL Developer), the result returned is correct without the extra decimals. I also noticed that this issue doesn't occur if the format of the column VAL is defined as NUMBER without format specification

For your information, I am using PL/SQL Developer 12.0.5.1828 (32 bit) on an Oracle 12c database.

 

[WexSoft]

I just tried your test in PL/SQL Developer 13.0.4.1906 connecting to an Oracle 11g database and it worked fine - no extra decimals.

 

[jlathion]

Hi WexSoft.

Thank you for the reply. I have also tried with the latest version on my side and it works if I use the 64-bit version of the IDE. However, the problem still occurs if I use the 32-bit one.

For information, the application runs on my side on a Windows 2012 R2 64-bit server, but for technical reason we have to use the 32-bit version of the Oracle client and that's why we are also using the 32-bit version of PL/SQL Developer.

 

[WexSoft]

I have a Windows XP VM which is 32-bit, and I just tried your test on that (using version 12.0.7.1837 of PL/SQL Developer, connecting to Oracle 11g), and sure enough I was able to recreate the error.
So yes there would appear to be an issue with the 32-bit version.

 

[Marco Kalter]

Can you go to Preferences > SQL Window, enable the "Number fields to_char" option, and try again?

The data is inserted correctly, but it may be that floating point precision restrictions are causing this issue. Enabling the preference above will fetch the numbers as character values with full database precision.

 

[WexSoft]

I can confirm that turning on the setting for "Number fields to_char" fixes the issue on version 12.0.7.1837 running against Oracle 11g in Win XP VM, although the output then truncates the trailing zeroes, so you get 123456789012.22 instead of 123456789012.220000.

 

[jlathion]

I tried on my side as well and the number is now correctly displayed. Thank you