About oralce numerical Storage

During dump today, I checked some information about Oracle's digital storage rules, which is good.

Http://www.jlcomp.demon.co.uk/number_format.html (from: Jonathan Lewis)

Simple translation:

Jl computer consultancy --- a consulting company

How are numbers stored.
How to store numbers in Oracle databases

November 2001 --- initial version
Updated: Feb 1020 -- Updated on January 1, February 2010

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, so I did a few quick tests with the dump () function, and came up with the following.
In May November 2001, someone asked me a question about how to convert an oracle internal database format to a format that humans can understand. I remember how to handle this in an Oracle document, however, it takes a lot of time to find out this document, so I used another method to quickly use the function dump for some tests, the following is a summary.
If you want to repeat the experiments-the command is ::
If you want to repeat these experiments, run the following command:
Select dump ({number}, 10) from dual; -- decimal dump
Export in decimal format
Select dump ({number}, 16) from dual; -- Hex dump
Export in hexadecimal format
The rules of the format appear to be as follows:
Rule conversion rules are roughly as follows:
Step 1: Precision
Step 1: Accuracy
If the internal representation of the number ends in 102 (hex 0x66)
If the number is expressed internally as 102 (hexadecimal 0x66 ),
It is negative
Indicates that this is a negative number.
Discard the last number (102/0x66)
Remove the last number (102/0x66)
Subtract the 2nd through to penultimate numbers from 101 (hex 0x65)
For each number starting from the second digit to the last digit, the value is reduced by 101 () and a new number is obtained.
Else
It is positive
The number does not end with 102, so this number is a positive number.
Subtract one from the 2nd through to final numbers
For each digit starting from the second digit to the last digit, subtract 1 to get a new number.
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.
For each number converted above, we use two digits (for example, if needed, we need to add 0 before) and add a decimal point after the first converted number.

Step 2: Scale
Part II scaling ratio
If the number is negative (ended in 102/0x66) then
If the number is negative
The first number of the internal representation is 62 (hex 0x3e) plus the power of 100 to divide
Then the first internal number is the sum of 62 and an index, which is used above 100 and the result is used for Division.
Else
The first number of the internal representation is 193 (hex 0xc1) plus the power of 100 to multiply
Then the first internal number is the sum of 193 and an index, which is used above 100 and the result is used for multiplication.
End if.
There is one special case-zero is represented by a single byte 0x80.
However, there is a special case where 0 is represented as a single byte 0x80

Example 1: -- Example 1
C3 2 2e = 195 2 46
The number is positive (doesn' t end in 102/0x66)
This number does not end with 102, so it is positive.
(2, 46) --> 01 45 --> 1.45
2-1 = 1 01
46-1 = 45 45
The first digit here is 01, so the result is 01.45.
195 = 193 + 2 --> multiply by 100 twice
The exponential part is 2, that is, the 2nd power of 100 is 10000
Answer 14,500
So the result is: 1.45*10000 = 14500

Example 2: --- Example 2
Be 2E 3D = 190 46 61
The number is positive
This number does not end with 102, so it is positive.
(46, 61) --> 45 60 --> 45.60
46-1 = 45
61-1 = 60
The first number here is 45, so the result is 45.60.
190 = 193-3 --> negative so divide by 100 three times
The exponential part is-3, which means that the power of the negative 3 of 100 is 0.0000001.
Answer 0.0000456
Therefore, the result is 45.6*0.0000001 = 0.0000456.
Example 3: -- Example 3
40 1C 3D 66 = 64 28 60 102
The number is negative (ends in 102/0x66)
This number ends with 102, so it is negative.
(28, 61) --> 73 40 --> 73.40 (101-28 = 73 etc .)
101-28 = 73
101-61 = 40
The first number here is 73, so the result is-73.40.
64 = 62 + 2 --> divide by 100 two times
The exponential part is 2, that is, the 2nd power of 100 is 10000
Answer-0.00734
The result is-73.40/10000 =-0.00734.
Example 4: -- Example 4
3C 5d 8 25 43 66 = 60 93 8 37 67 102
The number is negative
This number ends with 102, so it is negative.
(93, 8, 37, 67) --> 8, 93, 64, 34 --> 8.936434
101-93 = 8 08
101-8 = 93
101-37 = 64
101-67 = 34
The first digit here is 08, so the result is-8.936434.
60 = 62-2 --> negative so multiple by 100 twice
The exponential part is-2, that is, the negative 2power of 100 is 0.00001.
Answer-89364.34
Therefore, the result is-8.936434/0.00001 =-89364.34.
Upgrade Feb 2010:
Updated on January 1, October 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:
I received a message from Steve Aaron at Barclays Capital, showing that a large negative number may not end with 102. Below is an example he gave.
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, 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 rule breaks.
Interestingly, the precision of the maximum value has exceeded 38 BITs that can be defined by Oracle. You cannot insert 40 bits into the database, the verification result shows that all numbers with over 38-bit precision will break this rule.

 

About oralce numerical Storage