In Oracle, the NUMBER is the main data type to store all numeric values including integers, fixed and floating point numbers.
In MariaDB, you can use TINYINT, SMALLINT, INT and BIGINT for integers, DECIMAL or NUMERIC for fixed point numbers, FLOAT and DOUBLE for approximate floating point numbers.
Oracle:
-- Sample table CREATE TABLE specs ( cnt1 NUMBER(5), -- Integer cnt2 NUMBER(15), cnt3 NUMBER(15, 2), -- Fixed point number cnt4 NUMBER, -- Exact floating point number cnt5 NUMBER(*) );
MariaDB:
-- Sample table CREATE TABLE specs ( cnt1 INT, -- Integer cnt2 BIGINT, cnt3 DECIMAL(15, 2), -- Fixed point number cnt4 DECIMAL(18, 6), -- Exact floating point number (see notes below) cnt5 DECIMAL(18, 6) );
Conversion summary:
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:
-- Sum 0.1 + 0.1 + 0.1 gives 0.30000000000000004, not 0.3 SELECT SUM(c1) FROM ( 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 DOUBLE fails on a key constraint:
Oracle:
-- Primary key on NUMBER column CREATE TABLE units ( val NUMBER NOT NULL PRIMARY KEY ); INSERT INTO units VALUES(10000000000000000000); # 1 row created. INSERT INTO units VALUES(10000000000000000001); # 1 row created.
MariaDB:
-- Primary key on DOUBLE column CREATE TABLE units ( val DOUBLE 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 converts NUMBER to DECIMAL(18,6) but if a column is the part of a primary key or unique constraint it is converted to INT by default.
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.