What is the maximum value for the Oracle number type

The number type of Oracle is composed of up to three parts, and these three sections are the highest bit representation bits, data parts, and sign bits. Where negative numbers contain sign bits, positive numbers do not include sign bits. In addition, the value 0 is more special, it contains only one numeric value the highest bit represents bit 80, there is no data part.

The highest bit of a positive number represents a bit greater than 80, and the highest bit of a negative number represents a bit less than 80. One of the highest digits of a positive number is the number of words, then the highest bit is C1, the hundred, the million in order to C2, C3, percentile, extremely for the C0, BF. A negative number of the highest bit of the words, the highest bit is 3E, the hundred, the Million is 3D, 3C, percentile, the number of points in turn is 3F, 40.

Each digit in the data section represents a 2-digit number. The two-digit number may be from 0 to 99, if the data itself is positive, then the binary 1 to 64 is used, if the data itself is negative, then the binary 65 to 2 is represented.

The sign bit is represented by 66.

The above is summed up by the dump results, and for these relational constants mentioned above, Oracle makes this choice reasonable, and we can deduce it later in the example, and further explain why this is the way to express it. The meaning of this list is to give you a general idea of the number type data first.

Let's explain in detail in an example below:

CREATE TABLETest_number (Number_col Number);INSERT  intoTest_numberVALUES(0);INSERT  intoTest_numberVALUES(1);INSERT  intoTest_numberVALUES(2);INSERT  intoTest_numberVALUES( -);INSERT  intoTest_numberVALUES(123);INSERT  intoTest_numberVALUES(4100);INSERT  intoTest_numberVALUES(132004078);INSERT  intoTest_numberVALUES(2.01);INSERT  intoTest_numberVALUES(0.3);INSERT  intoTest_numberVALUES(0.00000125);INSERT  intoTest_numberVALUES(115.200003);INSERT  intoTest_numberVALUES(-1);INSERT  intoTest_numberVALUES(-5);INSERT  intoTest_numberVALUES(-20032);INSERT  intoTest_numberVALUES(-234.432);SELECTNumber_col,DUMP(Number_col, -) D_number fromTest_number;

number_col	d_number
0	typ=2 len=1:80
1	typ=2 len=2:c1,2
2	typ=2 len=2:c1,3
25	typ=2 len=2:c1,1a
123	typ=2 len=3:c2,2,18
4100	typ=2 len=2:c2,2a
132004078	typ=2 len=6:c5,2,21,1,29,4f
2.01	typ=2 len=3:c1,3,2
0.3	typ=2 len=2:c0,1f
0.00	typ=2 len=3:be,2,1a
115.20	typ=2 len=6:c2,2,10,15,1,4
-1	typ=2 len=3:3e,64 , typ=2
-5	len=3:3e,60,66
-20032	typ=2 len=5:3c, 63,65,45,66
-234.43	typ=2 len=6:3d,63,43,3a,51,66

Each line is described below, based on the results from the example. First, two points are basic. The type=2 returned by the dump function indicates that the data type of dump is Number,length=n, which indicates that the value is stored in the database in the length n.

The result of 1.DUMP (0) is 0x80, as mentioned earlier, 0 has only a high position, no data bits. Because 0 is special, neither a positive number nor a negative number, so using a high position indicates that a bit with 80 is sufficient, does not conflict with other data, and Oracle saves some of the later data parts for space-saving considerations. But why does Oracle choose 0x80 to represent 0? We know that positive and negative numbers are opposite to each other, and each positive number has a corresponding negative. So if we are going to use encodings to represent numbers, then the encoding for positive and negative numbers should be halved, so that Oracle's representation of the data range is justified. The binary encoding of 0x80 is 1000 0000, which is exactly half the maximum value of a byte encoding, so it makes sense for Oracle to choose 0x80 to represent 0.

The result of 2.DUMP (1) is that 0XC102,0XC1 represents the highest bit, and 0x2 indicates that the value is 1. First, why does Oracle use C1 to represent bits? In fact, the truth is similar to that just now. Using the scientific notation, any real number s can be described as a.bx10n,a for the integer part, B for the fractional part, and N for the exponential portion of 10. When S is greater than 1 o'clock, n is greater than or equal to 0,s less than 1 o'clock, N is less than 0. That is, in the exponential way, when n is greater than 0 and N is less than 0, Oracle represents the broadest range. As a result, Oracle has chosen C1 to indicate that bits are the highest bit.

SELECTTo_char (ROUND(To_number ('Bayi','XXX')+(To_number ('FF','XXX')-To_number ('Bayi','XXX')+ 1)/2),'XX') fromDUAL;

C1

Why does Oracle use 0x2 to represent 1 instead of using 0x1 directly for 1? Oracle each byte represents a 2-digit number, so for this 2-bit number, there may be a total of 100 0~99, the problem is 0 here. Oracle's underlying is implemented in C, and we know that binary 0 is used as a string terminator in the C language, so that Oracle avoids this problem by using a 0x1 representation of 0, and so on, using 0x64 to represent 99.

The result of 3.DUMP (2) is 0xc103.

The result of 4.DUMP (25) is 0xc11a. As mentioned earlier, the data section is saved in the smallest unit of 2 bits. So for 25来 said that the highest bit is still the bit, the value on the single digit is 25, according to the rules introduced above, 25 is stored as 0xc11a.

SELECT To_char (+1'xx' from

To_char (25+1, ' XX ')

1a

The result of 5.DUMP (123) is 0xc20218. Since 123 is the highest of the hundred, so the highest bit is 0xc2, the Hundred is 1, with 0x02, the single digit is 23, expressed in 0x18.

The result of 6.DUMP (4100) is 0xc22a.
Note that if the number on the last digit is 0,oracle out of space-saving considerations it is not stored. For example: 4,100 to save only the 41,12000000 on the hundred-bit 12,512000 only save the million on the 51 and hundreds on the 20.

The result of 7.DUMP (132004078) is 0xc5022101294f. The highest bit is billion, so with 0xc5 said, Billion is 1 with 0x02 said, hundred bit is 32 with 0x21 said, million is 0 with 0x01 said, Hundred is 40 with 0x29, single on 78 with 0x4f said.
Note: 0 on the middle digits cannot be omitted.

The result of 8.DUMP (2.01) is 0xc10302. The highest bit is single-digit with 0XC1, the single-digit is 2 with 0x03, percentile is 1 with 0x02 expression.
Note: The next digit is percentile is not very bit.

The result of 9.DUMP (0.3) is 0xc01f. The highest bit is percentile, using 0xc0 to indicate that the percentile is 30 with 0x1f.

The result of 10.DUMP (0.00000125) is 0xbe021a. The highest bit is the million-bit, in 0xBE, the highest 1 with 0x02, 25 expressed by 0x1a.

The result of 11.DUMP (115.200003) is 0xc20210150104.

The result of 12.DUMP (-1) is 0x3e6466. The highest bit, expressed in 0x3e, 64 means that the digit is 1,66 is the sign bit, indicating that the number is negative.

Negative and positive numbers are opposite to each other, and the highest bit of a negative number indicates that the value of the highest bit and its corresponding inverse count is ff. The highest bit of 1 indicates that the bit is the highest bit of c1,-1 and the bit is 3E. Negative numbers in 1 are denoted by 64. The number in a negative number and its inverse data are added as 0X66, which is the sign bit. Positive 1 is expressed in 0x02, negative 1 is represented by 0x64, and the sum of the two is 0x66. Negative number one identity bit, expressed in 0x66. Since the representation range of positive numbers is 0x01 to 0x64, the representation range of negative numbers is 0x65 to 0x02. Therefore, there is no 0x66 representation that occurs when a number is represented.

The result of 13.DUMP (-5) is 0x3e6066. 0X3E indicates that the highest level is the digit, 0x60 indicates that the 5,0x66 is the symbol identifier bit. 0X3E plus 0xc1 is 0xFF. The result of 0x60 plus 0x06 is 0x66.

The result of 14.DUMP (-20032) is 0x3c63654566. The highest bit is the million bit, the positive number is 0xc3, so the negative number of the million bits is 0x3c. Million position is 2, positive number with 0x03, negative for 0x63, hundred is 0, positive with 0x01, negative use 0x65, the digit is 32, positive number with 0x21, negative use 0x45 to express. 0X66 is a negative number that represents a bit.

The result of 15.DUMP (-234.432) is 0x3d63433a5166.

Depending on the storage characteristics of Oracle, you can also roll out the range of values for the number type of Oracle. This is described on Oracle's concept:
The following numbers can be stored in a number column:
Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 with up to $ significant digits.
Negative numbers from-1 x 10^-130 to 9.99...99 x 10^125 with up to $ significant digits.
Zero.

The range of values is deduced below.
Look at the sign bit, 0xc1 represents the digit.

Select to_number ('ff'xxx'- to_number ( ' C1 ' ' XXX '  from dual;

To_number (' FF ', ' xxx ')-to_number (' C1 ', ' xxx ')

62

Since Oracle is two-bit, two-bit storage, the highest bit is equivalent to 62x2=124, and the maximum value is 99, so the maximum value for a positive number is 9.999......x10^125.

Select to_number ('C1'xxx'- to_number ( '  the ' ' XXX '  from dual;

To_number (' C1 ', ' xxx ')-to_number (' Up ', ' xxx ')

65

The highest bit is equivalent to 65x2=130, so the minimum value for a positive number is 1x10^-130. Negative and positive numbers use half the code each, so they have the same extremum range.

What is the maximum value for the Oracle number type