NUMBER Data Type - Oracle to MariaDB Migration

In Oracle, the NUMBER is the main data type to store all numeric values including integers, fixed and floating point numbers.

In MariaDB Oracle compatibility mode, you can also use the NUMBER data type but note some issues and restrictions outlined below.

Oracle:

  -- Sample table  
  CREATE TABLE specs
  (
    cnt1 NUMBER(5),           -- Integer 
    cnt2 NUMBER(15),
    cnt3 NUMBER(15, 2),     -- Fixed point number
    cnt4 NUMBER(*, 2),
    cnt5 NUMBER,               -- Exact floating point number
    cnt6 NUMBER(*)
  );

MariaDB - Oracle Compatibility:

  -- Sample table  
  CREATE TABLE specs
  (
    cnt1 NUMBER(5),             -- Integer 
    cnt2 NUMBER(15),
    cnt3 NUMBER(15, 2),       -- Fixed point number
    cnt4 NUMBER(38, 2),       -- (*) syntax is not supported
    cnt5 NUMBER,                 -- Inexact floating point number (see notes below)
    cnt6 NUMBER                  -- (*) syntax is not supported
  );

If you run SHOW CREATE TABLE for the table above, you can see how MariaDB represents this table internally:

MariaDB - Oracle Compatibility:

  CREATE TABLE "specs" (
    "cnt1" decimal(5,0) DEFAULT NULL,
    "cnt2" decimal(15,0) DEFAULT NULL,
    "cnt3" decimal(15,2) DEFAULT NULL,
    "cnt4" decimal(38,2) DEFAULT NULL,
    "cnt5" double DEFAULT NULL,
    "cnt6" double DEFAULT NULL
  )

Note that NUMBER(p,s) is represented as the DECIMAL data type while NUMBER as the DOUBLE data type.

The NUMBER or NUMBER(*) data type without precision and scale can store exactly any arbitrary number. At the same time the MariaDB DOUBLE is an inexact (approximate) data type:

MariaDB - Oracle Compatibility:

  -- Sum 0.1 + 0.1 + 0.1 gives 0.30000000000000004, not 0.3
  SELECT SUM(c1)
  FROM 
    (
       -- MariaDB does not support CAST AS NUMBER
       SELECT CAST(0.1 AS DOUBLE) c1
       UNION ALL
       SELECT CAST(0.1 AS DOUBLE) c1
       UNION ALL
       SELECT CAST(0.1 AS DOUBLE) c1
     ) t;
 
     # sum
     # ---------------------
     # 0.30000000000000004

At the same time, using MariaDB DECIMAL data type:

  -- Sum is correct now
  SELECT SUM(c1)
  FROM 
    (
       SELECT CAST(0.1 AS DECIMAL(18,6)) c1
       UNION ALL
       SELECT CAST(0.1 AS DECIMAL(18,6)) c1
       UNION ALL
       SELECT CAST(0.1 AS DECIMAL(18,6)) c1
     ) t;
 
     # sum
     # -----
     # 0.3

Consider one more example when NUMBER as DOUBLE fails on a key constraint:

Oracle:

  -- Primary key on NUMBER column
  CREATE TABLE units 
  (
    val cNOT NULL PRIMARY KEY
  );
 
  INSERT INTO units VALUES(10000000000000000000);
  # 1 row created.
 
  INSERT INTO units VALUES(10000000000000000001);
  # 1 row created.

MariaDB - Oracle Compatibility:

  -- Primary key on NUMBER column
  CREATE TABLE units 
  (
    val NUMBER NOT NULL PRIMARY KEY
  );
 
  INSERT INTO units VALUES(10000000000000000000);
  # Query OK, 1 row affected (0.02 sec)
 
  INSERT INTO units VALUES(10000000000000000001);
  # ERROR 1062 (23000): Duplicate entry '1e19' for key 'units.PRIMARY'

By default, SQLines SQL Converter uses the NUMBER data type in MariaDB Oracle compatibility mode.

You can modify the default conversion and set any other data type including DOUBLE using -ora_number and -ora_number_int configuration options.

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.