Is "NUMBER" and "NUMBER(*,0)" the same in Oracle?
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>
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.