Understanding NUMBER Datatype in Oracle Database

The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision.

The following numbers can be stored in a NUMBER column:

• Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits
• Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits
• Zero
• Positive and negative infinity (generated only by importing from an Oracle Version 5 database)

For numeric columns, you can specify the column as:

column_name NUMBER 

Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):

column_name NUMBER (precision, scale) 

If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.

Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:

column_name NUMBER (*, scale) 

In this case, the precision is 38, and the specified scale is maintained.

When you specify numeric fields, it is a good idea to specify the precision and scale. This provides extra integrity checking on input.

 The following table shows examples of how data would be stored using different scale factors.

Input Data	Specified As	Stored As
7,456,123.89	NUMBER	7456123.89
7,456,123.89	NUMBER(*,1)	7456123.9
7,456,123.89	NUMBER(9)	7456124
7,456,123.89	NUMBER(9,2)	7456123.89
7,456,123.89	NUMBER(9,1)	7456123.9
7,456,123.89	NUMBER(6)	(not accepted, exceeds precision)
7,456,123.89	NUMBER(7,-2)	7456100

If you specify a negative scale, Oracle rounds the actual data to the specified number of places to the left of the decimal point. For example, specifying (7,-2) means Oracle rounds to the nearest hundredths, as shown in the above table.

For input and output of numbers, the standard Oracle default decimal character is a period, as in the number 1234.56. The decimal is the character that separates the integer and decimal parts of a number. You can change the default decimal character with the initialization parameter NLS_NUMERIC_CHARACTERS. You can also change it for the duration of a session with the ALTER SESSION statement. To enter numbers that do not use the current default decimal character, use the TO_NUMBER function.

Internal Numeric Format

Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

Taking this into account, the column size in bytes for a particular numeric data value NUMBER(p), where p is the precision of a given value, can be calculated using the following formula:

ROUND((length(p)+s)/2))+1

where s equals zero if the number is positive, and s equals 1 if the number is negative. For example, both 999999999 and 9999999.99 will require 6 bytes to store them.

select round((length(999999999)+0)/2)+1 from dual;

ROUND((LENGTH(999999999)+0)/2)+1
--------------------------------
                               6

Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.

Number vs. Number(n)

number(n) is a number with a constraint and edit.

create table t ( x number, y number(5) );

Table created.

insert into t values ( 123.456, 123.456 );

1 row created.

insert into t values ( 123.999, 123.999 );

1 row created.

insert into t values ( 12345, 12345 );

1 row created.

insert into t values ( 123456, 123456 );

insert into t values ( 123456, 123456 )
                               *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


select * from t;

         X          Y
---------- ----------
   123.456        123
   123.999        124
     12345      12345

 

See how the number(5) behaves?  5 digits, no decimals, rounded. 

The advantage is

a) you have 5 digits only, never more

b) you have no decimals, it is an integer type

consider it a constraint -- like a primary key, NOT NULL, check, whatever. 

If your data is such that the number should never be more then 5 digits, the only correct way to implement it would be as a number(5) .