Is "NUMBER" and "NUMBER(*,0)" the same in Oracle?

Question

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?

Answer 1

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>