command window displays numbers incorrect

[Raimund Kelterer]

hello all,

the following sql statement shows the incorrect values of the function in column N. the column N_TO_CHAR shows the values converted by the database already - these are right. I even tried to define a format for the column N - it did not work.

-- start of script
select i
, 1 / power(exp(1),i) n
, to_char (1 / power(exp(1),i)) n_to_char
from (
select level i
from dual connect by level < 38
);
-- end of script

is this a bug - if not - how can a solve this.

with the best wishes from austria
raimund

 

[Roeland]

I see no problem on our side. What version of oracle are you using?

Tested this on an Oracle 12.1.0.1

 

[Raimund Kelterer]

I tested it with 12.1.0.1.0(64bit) and 10.2.0.4.0(32bit) - both show the same result.

the display shows the following


SQL> set numwidth 12
SQL> /
I N N_TO_CHAR
------------ ------------ ----------------------------------------
1 0,3678794411 ,367879441171442321595523770161460867445
2 0,1353352832 ,135335283236612691893999494972484403407
3 0,0497870683 ,049787068367863942979342415650061776632
4 0,0183156388 ,018315638888734180293718021273241242212
5 0,0067379469 ,006737946999085467096636048423148424249
6 0,0024787521 ,002478752176666358423045167430816667891
7 0,0009118819 ,000911881965554516208003136084409282626
8 0,0003354626 ,000335462627902511838821389125780861019
9 0,0001234098 ,000123409804086679549497636690730033826
10 4,5399929762 ,00004539992976248485153559151556055061
11 1,6701700790 ,000016701700790245659312635517360580879
12 6,1442123533 6,1442123533282097586823081788055323E-06
13 2,2603294069 2,2603294069810543257852772905386895E-06
14 8,3152871910 8,3152871910356788406398514256526229E-07
15 3,0590232050 3,0590232050182578837147949770228964E-07
16 1,1253517471 1,1253517471925911451377517906012719E-07
17 4,1399377187 4,1399377187851666596510277189552806E-08
SQL> set numwidth 20
SQL> /
I N N_TO_CHAR
-------------------- -------------------- ----------------------------------------
1 0,367879441171442 ,367879441171442321595523770161460867445
2 0,135335283236613 ,135335283236612691893999494972484403407
3 0,0497870683678639 ,049787068367863942979342415650061776632
4 0,0183156388887342 ,018315638888734180293718021273241242212
5 0,00673794699908547 ,006737946999085467096636048423148424249
6 0,00247875217666636 ,002478752176666358423045167430816667891
7 0,000911881965554516 ,000911881965554516208003136084409282626
8 0,000335462627902512 ,000335462627902511838821389125780861019
9 0,00012340980408668 ,000123409804086679549497636690730033826
10 4,53999297624848E-5 ,00004539992976248485153559151556055061
11 1,67017007902456E-5 ,000016701700790245659312635517360580879
12 6,14421235332821E-6 6,1442123533282097586823081788055323E-06
13 2,26032940698105E-6 2,2603294069810543257852772905386895E-06
14 8,31528719103568E-7 8,3152871910356788406398514256526229E-07
15 3,05902320501826E-7 3,0590232050182578837147949770228964E-07
16 1,12535174719259E-7 1,1253517471925911451377517906012719E-07
17 4,13993771878517E-8 4,1399377187851666596510277189552806E-08

-- my NLS Setting
SQL> select * from nls_session_parameters;
PARAMETER VALUE
-------------------------------------------------------------------------------- ----------------------------
NLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
NLS_CURRENCY

 

[Hilarion]

OK. To make things more clear, the problem (beside small rounding differences) is that when scientific notation kicks in (rows numbered from 10 upwards), do not show the exponent value when "numwidth" is low (e.g. 12).
For me it was easier to notice it when I actually made the third column to look similar to the second one, so I do not get lost into all those digits. I did it by introducing a formatting pattern for the TO_CHAR function:

set numwidth 12

SELECT i,
       1 / POWER(EXP(1), i) n,
       TO_CHAR(1 / POWER(EXP(1), i),'0.9999999999') AS n_to_char
  FROM (SELECT LEVEL AS i
          FROM dual
        CONNECT BY LEVEL < 38)
 ORDER BY 1;

It seems that the PLD does not take the exponent into account when calculating how much of the number will fit the set column width.

 

[Raimund Kelterer]

any news on this issue ?