13

In Oracle documentation it is mentioned that

NUMBER (precision, scale)

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

But NUMBER (without precision and scale) is also accepting floating point numbers (34.30) but according to documentation if scale is not specified it should be zero scale by default so it should allow only integers, am I wrong?.

And in another questions it is mentioned that

default precision is 38, default scale is zero

So NUMBER and NUMBER(*,0) should be equal but they are not.

Where am I wrong?

Community
  • 1
  • 1
Rajeev
  • 843
  • 2
  • 11
  • 22

4 Answers4

17

I think the sentence in the documentation

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

is a bit confusing. The scale is zero if a precision is specified and a scale is not specified. So, for example, NUMBER(19) is equivalent to NUMBER(19,0). NUMBER, by itself, will have 38 digits of precision but no defined scale. So a column defined as a NUMBER can accept values of any scale, as long as their precision is 38 digits or less (basically, 38 numerical digits with a decimal point in any place).

You can also specify a scale without a precision: NUMBER(*, <scale>), but that just creates the column with 38 digits of precision so I'm not sure it's particularly useful.

The table How Scale Factors Affect Numeric Data Storage on this page might be helpful.

David Faber
  • 12,277
  • 2
  • 29
  • 40
13

The default of scale is not zero, which has no value in it. Hence it can accept any value between -84 to 127. If you limit it to zero then it will not accept any presicion even the value contains the scale value 

create table aaaaa
(
sno number(*,0),
sno1 number
);

The user_tab_columns will give you the value of your precision and scale 

SQL> select column_name,data_precision,data_scale from user_tab_columns where ta
ble_name = 'AAAAA';

COLUMN_NAME                    DATA_PRECISION DATA_SCALE
------------------------------ -------------- ----------
SNO                                                    0
SNO1

SQL>

Please find the below workings

SQL> select * from aaaaa;

no rows selected

SQL> insert into aaaaa values (123.123123,123123.21344);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from aaaaa;

       SNO       SNO1
---------- ----------
       123 123123.213

SQL>
Exhausted
  • 1,867
  • 2
  • 23
  • 33
  • 2
    Why is the output of your "select" statement limited to "123123.213" for column 'SNO1'? Is this a limitation of your SQL client? Since "NUMBER" sets no scale limitation, the result should be "123123.21344" (with five digits after the decimal point). – Peter A Sep 29 '17 at 11:57
  • As a complement, the NUMBER(\*,0) is equivalent to write INT or INTEGER as type. It's visually very clear and you'll see it creates a NUMBER(38,0). There's some discussion about certain difference between NUMBER(\*,0) and NUMBER(38,0) but I imagine it's very subtle. – Raúl Moreno Apr 17 '20 at 15:40
4

This parts of Oracle documentation makes it absolutely clear:

Specify an integer using the following form:

NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

and

Specify a floating-point number using the following form:

NUMBER

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

The meaning of the star precision is documented here and means the precision of 38

Community
  • 1
  • 1
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
0

Another squirrelly case but one I faced... if the table you're inserting into contains a trigger, you should probably examine if any of its procedural flow includes attempting to convert something into a NUMBER...

Eric Kramer
  • 192
  • 2
  • 7