While I was working on Oracle last year, I always wondered how & what to chose as the data type for my numeric fields. The source of this post gave me a deep insight into this question and helped me achieve the best.

Excerpts from the source are listed here for easy access..

Differences between Number & Number(p,s):

As a decimal type, NUMBER allows you to indicate the precision (total number of digits) and scale (number of digits to the right of the decimal point) when defining a field of this type (NUMBER(p, s)).

Examples:

NUMBER(9, 2): Nine significant digits in total (precision) of which 2 (scale) may be used for the decimal part of the value (digits to the right of the decimal point).

NUMBER(9): Nine significants digits in total, none of them for the decimal part. Yes, that’s the way to restrict your fields for integer values storage.

NUMBER: “I will save whatever you give me” with an accuracy of up to 38 significant digits.

NUMBER(*,2): You set no limit to the precision but reducing (rounding) the decimal part to two digits.

NUMBER(9,-2): Nine digit for the integer part which will be “rounded” at the last two digits (interesting), i.e.: 987,654,321 -> 987,654,300.

There are 2 advantages of using Number(p,s) a=rather than just Number data type:

a) To restrict the entry of data: If we specify precision and scale, we are adding a restriction that allows us to establish a greater shielding on the data (the more “downstream” the better, and the shield will apply to any application developed over this database).

Problem: It is vital to know precisely in advance the needs of the field, which is not sometimes easy. For a field which is, for instance, intended to hold the surface of a construction in a cadastral application, precision and scale could be set without further problems (usually a two digit scale for area values in square meters).

But what precision and scale should be assigned to a coefficient K that can be fixed arbitrarily by a per year shifting taxation law? Perhaps what today is a ratio of two decimal digits, tomorrow will have six, causing to have to redefine the structure of the table every year with the usual associated impact in a productive environment.

b) The saving of disk space: It is common thinking that if you reduce precision the needs of storage cost will be reduced in the same meassure, and therefore you will save disk space.

Detailed explanation can be found here.