Number

From Oracle FAQ

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.

Contents 1 Examples 2 Internal storage 3 Test if string is numeric 4 Also see

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

Test if string is numeric

The following function can be used to test is 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	#