6

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.

??

Marcus Leon
  • 55,199
  • 118
  • 297
  • 429
  • 1
    Not sure about TOAD, but maybe there is an equivalent to the SQL*Plus "numformat" setting ? (http://www.adp-gmbh.ch/ora/sqlplus/numformat.html) – monojohnny Aug 03 '10 at 20:23
  • 1
    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. – Jeffrey Kemp Aug 04 '10 at 04:51

3 Answers3

12

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.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
  • 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. – Mysterious Dan Feb 05 '14 at 00:33
4

-> 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

armatita
  • 12,825
  • 8
  • 48
  • 49
Tim
  • 41
  • 2
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.

enter image description here

I hope that helps.

Marwan مروان
  • 2,163
  • 8
  • 30
  • 40
  • This does make a difference for em as well, however, if I am running a script/PL/SQL, then this is not an option. – Gaffi Mar 12 '14 at 13:47