I was reading the WorkflowFaq Blog today, where they discuss when 1 != 1 in Oracle.

Of I did some testing with this. First of course I reproduced the issue easy enough:

SQL> select 1/3*3 from dual;

1/3*3 ---------- 1

SQL> select * from dual where 1/3*3=1;

No rows selected.

It is obviously a rounding issue. So what happens if we force Oracle to round the result?

SQL> select * from dual where round(1/3*3)=1;

D - X

If the rounding went wrong, it will most likely be on the low side (0.3333……*3=0.9999……)

SQL> select trunc(1/3*3) from dual where ceil(1/3*3)=1;

D - 0

We can see the real contents by dumping the result:

SQL> select dump(1/3*3) d from dual 2 union 3 select dump(1) d from dual;

D -------------------------------------------------------------------------------- Typ=2 Len=21: 192,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,10 0,100,100,100,100

Typ=2 Len=2: 193,2

I will go into the internal number format in a while. For now, it is sufficient to say that the 1/3*3 is indeed 0.9999999…….

Now how many decimals can we get?

SQL> select to_char(1/3*3,'9.99') from dual;

TO_CH ----- 1.00

SQL> select to_char(1/3*3,'9.99999999999999999999999999999999999999999') from dual;

TO_CHAR(1/3*3,'9.999999999999999999999999999 -------------------------------------------- .99999999999999999999999999999999999999990

There is the real value of 1/3*3.

The Oracle documentation says that the precision of the number format is 126 binary digits, approximately 38 decimals digits.

So when we force a mask with more decimals, Oracle will show the real value.

Àn interesting 'feature'. And definitely something to keep in mind.

One last note on the dumping of numbers.

dump(<number>,10) will dump the decimal value.

dump(<number>,16) will dump the hexadecimal value.

How do we translate it back to the real value then?

Let's do the excercise for a few numbers: 1234.5678 and -1234.5678.

There is some difference between positive and negative values. So let's do the positive one first.

SQL> select dump(1234.5678,10),dump(1234.5678,16) from dual;

DUMP(1234.5678,10) DUMP(1234.5678,16) ---------------------------- -------------------------- Typ=2 Len=5: 194,13,35,57,79 Typ=2 Len=5: c2,d,23,39,4f

We start with the second to the last numbers. And we subtract 1:

194,12,34,56,78

Ignore the 194 for now, and put the decimal . after the second number: 12.345678

Now subtract 193 from the first value: 194-193=1.

Multiply the decimal value this number of times by 100: 12.345678 * 100 = 1234.5678.

(In our case we multiply by 100 once if the first value would be higher, mulitply more often).

The negative values are a little different:

SQL> select dump(-1234.5678,10),dump(-1234.5678,16) from dual;

DUMP(-1234.5678,10) DUMP(-1234.5678,16) ------------------------------- ------------------------------ Typ=2 Len=6: 61,89,67,45,23,102 Typ=2 Len=6: 3d,59,43,2d,17,66

The 102 at the end means it is a negative value. Discard the 102 for the rest of the calculation.

61,89,67,45,23.

Subtract the second to the last number from 101.

101-89=12, 101-67=34, 101-45=56,101-23=78 gives

61,12 34 56 78

Put the decimal . after the second number. So we get 12.345678

Now subtract 62 from the first value and multiply by 100: 61-62 = -1.

Finally we divide the value this number of times by 100. In our case we have a negative, so we

multiply instead:

12.345678 * 100 = 1234.5678 -> And we had a negative. So -1234.5678