Number format

Number format

JL Computer Consultancy

I was recently (Nov 2001) asked to explain how to translate a number fromOracle's internal database format to a human readable form. I know that I have seena description somewhere in one of the manuals of how Oracle does this, but italways takes ages to find anything in the manuals when I really want to, so Idid a few quick tests with the dump() function, andcame 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 whereneeded). 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 verylarge 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 to40 digits of precision into the database – and it seemsto be numbers that go beyond 38 digits of precision where the rule breaks.