Search code examples
oraclesqldatatypes

What is the difference between Number(38,127), Number, Number(*) and Number(*,127) in Oracle

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?

Solution

  • 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.

    https://dbfiddle.uk/1TAnV73u