JL Computer Consultancy

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

Update Feb 2010:

I’ve been sent an example by Steve Aaron of Barclays Capital  show that the “negative number end in 0x66” rule breaks down for very large numbers. Here’s an extract from his demonstration:

create table numtest (n number);
复制

insert into numtest values (-1111111111111111111111111111111111110703);
复制

 
复制

column n       format 9,999,999,999,999,999,999,999,999,999,999,999,999,999
复制

column dumped  format a78
复制

 
复制

select n, dump(n,16) dumped from numtest;
复制

                                                     N
复制

------------------------------------------------------
复制

DUMPED
复制

------------------------------------------------------------------------------
复制

-1,111,111,111,111,111,111,111,111,111,111,111,110,703
复制

Typ=2 Len=21: 2b,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5a,5e,62
复制

 
复制

1 row selected.
复制

Interestingly, the largest precision you can define for a number is number(38) – but if you don’t declare a precision you can get number of up to 40 digits of precision into the database – and it seems to be numbers that go beyond 38 digits of precision where the rule breaks.