Oracle use Number to store the fixed-point and floating-point value and we can choose to specify the value of either precision or scale.
My understanding is that Number(38, 127) defines a fixed-point number with only a fractional part, and the last three can be used to store floating-point numbers. However, when I tried to, for example, insert the value 1e-130 for these four columns, Number(38, 127) and Number(,127) store it as 0 while Number and Number() store it as 1e-130, which again appears to be different from my previous understanding.
So I'm wondering what the difference is between the four definitions as described in my title?
If you have the table:
CREATE TABLE table_name (
a NUMBER,
b NUMBER(*),
c NUMBER(*,127),
d NUMBER(38,127)
);
and you look at the meta-data:
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_cols
WHERE table_name = 'TABLE_NAME';
COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_PRECISION | DATA_SCALE |
---|---|---|---|---|
A | NUMBER | 22 | null | null |
B | NUMBER | 22 | null | null |
C | NUMBER | 22 | null | 127 |
D | NUMBER | 22 | 38 | 127 |
or:
SELECT dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', USER)
FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME',USER) |
---|
CREATE TABLE "FIDDLE_BVCZPJIEOISTSDBTPGEL"."TABLE_NAME" ( "A" NUMBER, "B" NUMBER, "C" NUMBER(*,127), "D" NUMBER(38,127) ) ; |
Then you can see that NUMBER
and NUMBER(*)
are identical - and NUMBER(*)
is displayed as just NUMBER
when you generate the DDL statement for the table.
To see the practical differences between the four:
INSERT INTO table_name (a, b, c, d) VALUES (1e-130, 1e-130, 1e-130, 1e-130);
INSERT INTO table_name (a, b, c, d) VALUES (1e-127, 1e-127, 1e-127, 1e-127);
INSERT INTO table_name (a, b, c, d) VALUES (1e125, 1e125, 1e125, 1e-90);
INSERT INTO table_name (d) VALUES (-0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999);
INSERT INTO table_name (d) VALUES (+0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999);
All succeed and:
INSERT INTO table_name (a) VALUES (1e126);
ORA-01426: numeric overflow
INSERT INTO table_name (b) VALUES (1e126);
ORA-01426: numeric overflow
INSERT INTO table_name (c) VALUES (1e126);
ORA-01426: numeric overflow
INSERT INTO table_name (d) VALUES (1e-89);
ORA-01438: value larger than specified precision allowed for this column
All fail with exceptions.
The table would then contain:
A | B | C | D |
---|---|---|---|
1.0E-130 | 1.0E-130 | 0.0E+00 | 0.0000000000000000000000000000000000000E+00 |
1.0E-127 | 1.0E-127 | 1.0E-127 | 1.0000000000000000000000000000000000000E-127 |
1.0E+125 | 1.0E+125 | 1.0E+125 | 1.0000000000000000000000000000000000000E-90 |
null | null | null | -9.9999999999999999999999999999999999999E-90 |
null | null | null | 9.9999999999999999999999999999999999999E-90 |
So:
NUMBER
and NUMBER(*)
are identical and can store numbers with a precision ranging from 1e-130 to 1e+125
NUMBER(*,127)
has a variable precision and a maximum scale of 127 and can store with a precision ranging from 1e-127 to 1e+125. If you try to store numbers with greater precision than 127 decimal digits then they will be rounded to the nearest multiple of 1e-127 (i.e. 1e-130 would round to 0).
NUMBER(38,127)
has a precision of 38, so it can store up to 38 significant digits and a scale of -127. It can store numbers from:
-0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999
(minus zero point 89 zeroes then 38 nines - giving a total of 127 decimal digits)
to
+0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999
(plus zero point 89 zeroes then 38 nines - giving a total of 127 decimal digits)
Again, 1e-130 is too precise for NUMBER(38,127)
and would be rounded to the nearest multiple of 1e-127, which is 0.