JL Computer Consultancy

How are numbers stored.

November 2001

I was recently (Nov 2001) asked to explain how to translate a number from Oracle's internal database format to a human readable form. I know that I have seen a description somewhere in one of the manuals of how Oracle does this, but it always takes ages to find anything in the manuals when I really want to, so I did a few quick tests with the dump() function, and came up with the following.

If you want to repeat the experiments - the command is::

        select dump({number},10) from dual;   -- decimal dump
        select dump({number},16) from dual;   -- hex dump

The rules of the format appear to be as follows:

Step 1: Precision
If the internal representation of the number ends in 102 (Hex 0x66)
        it is negative
        discard the last number (102 / 0x66)
        subtract the 2nd through to penultimate numbers from 101 (Hex 0x65)
else
        it is positive
        subtract one from the 2nd through to final numbers
end if

Write out each converted number using 2 digits (i.e. leading zeros where needed). Put a decimal point after the first adjusted number.

Step 2: Scale
If the number is negative (ended in 102 / 0x66) then
        The first number of the internal representation is 
               62 (hex 0x3e) plus the power of 100 to DIVIDE by
else
        the first number of the internal representation is 
               193 (hex 0xc1) plus the power of 100 to MULTIPLY by
end if.

There is one special case - zero is represented by a single byte 0x80.

Example 1:
c3 2 2e = 195 2 46
The number is positive (doesn't end in 102 / 0x66)
        (2,46) --> 01 45 --> 1.45
        195 = 193 + 2 --> multiply by 100 twice
        Answer 14,500
Example 2:
be 2e 3d = 190 46 61
The number is positive
        (46, 61) --> 45 60 --> 45.60
        190 = 193 - 3 --> negative so divide by 100 three times
        Answer 0.0000456
Example 3:
40 1c 3d 66 = 64 28 61 102
The number is negative (ends in 102 / 0x66)
        (28, 61) --> 73 40 --> 73.40          (101 - 28 = 73 etc.)
        64 =  62 + 2 --> divide by 100 two times
        Answer -0.00734
Example 4:
3c 5d 8 25 43 66 = 60 93 8 37 67 102
The number is negative
        (93, 8, 37, 67) --> 8 93 64 34  --> 8.936434
        60 = 62 - 2 --> negative so multiple by 100 twice
        Answer -89364.34

Back to Main Index of Topics