Number

NUMBER is a data type used to store numeric values.

Syntax:

NUMBER[(precision [, scale])]

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

Examples

• NUMBER(8,1) can store a maximum value for 9999999.9.
• NUMBER(8,6) can store a maximum value for 99.999999.
• NUMBER can store any numeric values between 1E-130 and 10E125

Internal storage

Numeric values are stored in compressed form. To see how numbers are stored internally:

SQL> select dump(123433, 16) from dual;

DUMP(123433,16)
-----------------------
Typ=2 Len=4: c3,d,23,22

As for most of the stored datatypes, numbers start with a byte giving the number of bytes of the stored data. This value is 255 (0xFF) if the value is NULL. We no more talk about this byte.

A not NULL value is represented in standard scientific notation of base 100, that is (number) = (sign) 0.(mantissa₁₀₀ * 100**(exponent₁₀₀) ; the first 2 numerals can't be 00.

The first byte represents the sign and the exponent, the following bytes represent each unit in the base 100 (that is 00 to 99 but the first and last bytes that can't be 00). The maximum size is 20 bytes for positive number or 21 bytes for negative ones.

In the end, the represented values can be the positive and negative values which absolute one is between 1.0*10^-130 and 9.999...999*10¹²⁵ (38 nines followed by 88 zeros) With a precision of 38 numerals (the 38^th can be inaccurate). To these values are added the special numbers 0, positive and negative infinite.

The first bit of the first byte is the sign: 1 for positive numbers, 0 for negative ones. The 7 other bits represent the exponent in the form: (exponent₁₀₀)+64 for positive numbers and 255-((exponent₁₀₀)+64) for negative ones.

The other bytes starting from the second one give the mantissa (in base 100) of the number. Each byte represents a number from 00 to 99. If X is the value of N^th number of the mantissa (in base 100), its representing value (the stored one) is X+1 if the (whole) number is positive and 101-X if it is negative. In addition, Oracle adds a tag byte of 102 (0x66) at the end of negative number. This byte and all the other encoding tricks seem to have the purpose to be able to use memcmp() to compare numbers and preserve the order.

For the special numbers the stored values are 128 for 0, the 2 bytes 255 and 101 for positive infinite and 0 for negative infinite.

Test if string is numeric

The following function can be used to test if a string is numeric or not:

CREATE OR REPLACE FUNCTION is_numeric(p_strval in varchar2) RETURN NUMBER
IS
  l_numval NUMBER;
BEGIN
  l_numval := TO_NUMBER(p_strval);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END is_numeric;
/

Also see

• ORA-01722 - invalid number

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#