Toad truncating/rounding large Oracle numbers?

Question

We have a table with a 'price' field of type NUMBER(20,7).. In TOAD I do this:

update mytable set price = 1234567890123.1234567;

Then I do this select:

select price, to_char(price) from mytable

PRICE              TO_CHAR(PRICE)
1234567890123.12   "1234567890123.1234567"

Question is, why does TOAD truncate the result when displaying the NUMBER(20,7) field? The data is obviously there as it prints out with to_char.

??

Recommend

Not sure about TOAD, but maybe there is an equivalent to the SQL*Plus "numformat" setting ? (adp-gmbh.ch/ora/sqlplus/numformat.html) – Onomasiology

My version of TOAD, 9.1.0.62, doesn't have any option except that very long numbers can be shown with scientific notation. Not that great - you'll have to use the TO_CHAR option when querying the table. – Mozarab

Answer 1

Toad limits numbers in the data grid to 15 digits. I believe this is because excel limits numbers to 15 digits as well (or used to limit them). You can turn on "scientific notation" in options -> data grids -> data, check box Display large numbers in scientific notation. That won't probably help, either.

Hardhack

It sort of sounds like a 53-bit Double significand vs. a 64-bit integral value. Losing 3 of the least significant digits = losing ~1000 ~= 2^10, which means we lost 10 bits out of 64 and are getting into the 53-bit realm. Just a random hypothesis, and I don't see any reason for them to be stuffing an integral value into a Double and back again. – Marenmarena

Answer 2

-> Right click on the data grid

-> Select "Grid Options..."

-> Under "Data Grids" chose "Data"

-> Uncheck "Display large numbers in Scientific Notation"

After doing these you need to close and open TOAD again. This worked for me in TOAD Version 8.6.0.38

Menorca

Answer 3

I had a similar problem, Toad was truncating/rounding my numbers when being displayed in grid view.

I found out that by using the Execute Statement button or by pressing F9 to run the query then the truncation/rounding would not be performed.

I hope that helps.

Loxodrome

This does make a difference for em as well, however, if I am running a script/PL/SQL, then this is not an option. – Huddleston