Oracle to MariaDB Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to MariaDB.

  • SQLines Data - Data transfer, schema migration and validation tool
  • SQLines SQL Converter - SQL scripts assessment and conversion tool

Databases:

  • Oracle 23, 22c, 21c, 19c, 18c, 12c, 11g, 10g and 9i
  • MariaDB 11.x, 10.x and 5.x

Starting from MariaDB 10.3 there is the Oracle compatibility mode, see Oracle to MariaDB Migration - Compatibility Mode.

Migration Reference

Identifiers

Max length:

Oracle MariaDB
Tables, columns, indexes, views, procedures, triggers 128 (was 30 before Oracle 12c) 64

SQL Language Elements

Converting SQL language elements:

Oracle MariaDB
1 -- comment Single line comment -- comment A blank is required after --

Oracle DATE arithmetic:

Oracle MariaDB
1 SYSDATE + 1 Add 1 day DATE_ADD(SYSDATE(), INTERVAL 1 DAY)
2 SYSDATE + 1/24 Add 1 hour DATE_ADD(SYSDATE(), INTERVAL 1 HOUR)
3 SYSDATE + 1/1440 Add 1 minute DATE_ADD(SYSDATE(), INTERVAL 1 MINUTE)
4 SYSDATE + 1/86400 Add 1 second DATE_ADD(SYSDATE(), INTERVAL 1 SECOND)

Operators and predicates:

Oracle MariaDB
1 (start1, end1) OVERLAPS (start2, end2) Check if two datetime
intervals overlap
(start2 < end1 AND end2 > start1)

Data Types

Character data types:

Oracle MariaDB
1 CLOB Character large object, 4 GB LONGTEXT
2 LONG Character data, 2 GB LONGTEXT
3 VARCHAR(n) Synonym for VARCHAR2 VARCHAR(n)
4 VARCHAR2(n) Variable-length string, 1 ⇐ n ⇐ 32,767 VARCHAR(n) 0 ⇐ n ⇐ 65,535

Unicode character data types:

Oracle MariaDB
1 NCHAR(n) Fixed-length Unicode string, n <= 2000 NCHAR(n) n <= 255
NVARCHAR(n) n > 255
2 NVARCHAR2(n) Variable-length Unicode string, n <= 4000 NVARCHAR(n) n <= 65,535
3 NCLOB Unicode large character object LONGTEXT

Numeric data types:

Oracle MariaDB
1 BINARY_FLOAT 32-bit floating-point number FLOAT
2 BINARY_DOUBLE 64-bit floating-point number DOUBLE
3 DECIMAL(p,s) Fixed-point number DECIMAL(p,s)
4 DOUBLE PRECISION Floating-point number DOUBLE PRECISION
5 FLOAT(p) Floating-point number DOUBLE
6 INTEGER, INT 38 digits integer INT DECIMAL(38)
7 NUMBER(p,0), NUMBER(p) 8-bit integer, 1 ⇐ p < 3 TINYINT (0 to 255)
16-bit integer, 3 ⇐ p < 5 SMALLINT
32-bit integer, 5 ⇐ p < 9 INT
64-bit integer, 9 ⇐ p < 19 BIGINT
Fixed-point number, 19 ⇐ p ⇐ 38 DECIMAL(p)
8 NUMBER(p,s) Fixed-point number, s > 0 DECIMAL(p,s)
9 NUMBER, NUMBER(*) Floating-point number DECIMAL(p,s) DOUBLE
10 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
11 REAL Floating-point number DOUBLE
12 SMALLINT 38 digits integer DECIMAL(38)

Date and time data types:

Oracle MariaDB
1 DATE Date and time with seconds DATETIME

Binary data types:

1 BLOB Binary large object, ⇐ 4G LONGBLOB
2 RAW(n) Variable-length binary data, 1 ⇐ n ⇐ 2000 VARBINARY(n)
3 LONG RAW Binary data, < 2G LONGBLOB

Other data types:

1 ROWID Physical row address CHAR(10)
2 XMLTYPE XML data LONGTEXT

Built-in SQL Functions

Converting string functions:

Oracle MariaDB
1 CHR(num) Get character from ASCII code CHR(num) since 10.3.1
2 INITCAP(str) Capitalize words in string User-defined function
3 INSTR(str, substr) Get position of substring INSTR(str, substr)
INSTR(str, substr, pos) LOCATE(substr, str, pos) param order
INSTR(str, substr, pos, num) User-defined function
4 LENGTH(string) Get length of string in chars CHAR_LENGTH(string)
5 LISTAGG(exp, delim)... Aggregate concatenation GROUP_CONCAT(exp SEPARATOR delim)
6 LPAD(string, len, pad) Pad the left-side of string LPAD(string, len, pad)
7 LTRIM(string) Remove leading spaces LTRIM(string)
LTRIM(string, set) Remove leading chars TRIM(LEADING set FROM string)
8 MONTHS_BETWEEN(dt1, dt2) Get difference in months User-defined function
9 REPLACE(str, search) Remove search-string REPLACE(str, search, ")
REPLACE(str, search, replace) Replace search-string REPLACE(str, search, replace)
10 RPAD(string, len, pad) Pad the right-side of string RPAD(string, len, pad)
11 RTRIM(string) Remove trailing spaces RTRIM(string)
RTRIM(string, set) Remove trailing chars TRIM(TRAILING set FROM string)
12 SUBSTR(string, pos, len) Get a substring of string SUBSTR(string, pos, len)
13 TO_CHAR(datetime, format) Convert datetime to string DATE_FORMAT(datetime, format)
TO_CHAR(number, format) Convert number to string FORMAT(number, decimal_digits)
14 TRANSLATE(string, from, to) Replace characters Nested REPLACE or User-defined function

Converting date and time functions:

Oracle MariaDB
1 ADD_MONTHS(date, num) Add num months to date TIMESTAMPADD(MONTH, num, date)
2 LAST_DAY(datetime) Get last day of the month,
includes time
LAST_DAY(datetime) Returns date only
3 SYSDATE Get current date and time SYSDATE() Includes time
4 TO_DATE(string, format) Convert string to datetime STR_TO_DATE(string, format)
TO_DATE(string) CAST(string AS DATETIME)

Converting NULL processing functions:

Oracle MariaDB
1 NVL(exp, replacement) Replace NULL with the specified value NVL(exp, replacement)

Converting math functions:

Oracle MariaDB
1 POWER(value, n) Raise value to the nth power POWER(value, n)

SELECT Statement

Converting queris:

Oracle MariaDB
1 SELECT * FROM (SELECT ...) Subquey alias is optional SELECT * FROM (SELECT ...) t Alias required
2 t1 JOIN t2 USING (column_name) USING clause for joins t1 JOIN t2 USING (column_name)
3 CONNECT BY PRIOR Hierarchical queries Recursive Common Table Expressions (CTE)
4 select INTERSECT select2 Select rows existing in both select INTERSECT select2 Since 10.3.0
5 ORDER BY col NULLS FIRST | LAST Order of NULLs ORDER BY col

Row limitation:

Oracle MariaDB - Before 10.6.1 MariaDB - Since 10.6.1
1 OFFSET k FETCH FIRST n ROWS ONLY Row limiting LIMIT k, n
2 ROWNUM = 1 Return 1 row only LIMIT 1 ROWNUM() = 1
ROWNUM <= n Row limit LIMIT n ROWNUM() <= n
ROWNUM < n LIMIT n - 1 ROWNUM() < n

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

Oracle MariaDB
1 GENERATED AS IDENTITY Identity column AUTO_INCREMENT
2 CONSTRAINT name NOT NULL Named NOT NULL constraint NOT NULL
3 ENABLE constraint attribute Removed

Storage and physical attributes:

Oracle MariaDB
1 PCTFREE num Removed
2 PCTUSED num Removed
3 INITRANS num Removed
4 MAXTRANS num Removed
5 COMPRESS [BASIC] | COMPRESS num | NOCOMPRESS Removed
6 LOGGING | NOLOGGING Removed
7 SEGMENT CREATION IMMEDIATE | DEFERRED Removed
8 TABLESPACE tablespace_name Removed
9 LOB (column) STORE AS BASIC FILE (params) Removed
10 PARALLEL num | PARALLEL (DEGREE n INSTANCES k) Removed
11 INMEMORY | NO INMEMORY Removed

STORAGE clause:

Oracle MariaDB
1 INITIAL num Removed
2 NEXT num Removed
3 MINEXTENTS num Removed
4 MAXEXTENTS num Removed
5 PCTINCREASE num Removed
6 FREELISTS num Removed
7 FREELIST GROUPS num Removed
8 BUFFER_POOL DEFAULT | KEEP | RECYCLE Removed
9 FLASH_CACHE DEFAULT | KEEP | NONE Removed
10 CELL_FLASH_CACHE DEFAULT | KEEP | NONE Removed

LOB storage clause:

Oracle MariaDB
1 TABLESPACE name Removed
2 DISABLE | ENABLE STORAGE IN ROW Removed
3 CHUNK num Removed
4 NOCACHE Removed
5 LOGGING Removed

CREATE VIEW Statement

Converting views:

Oracle MariaDB
1 CREATE OR REPLACE VIEW name CREATE OR REPLACE VIEW name
2 FORCE keyword Create view even if tables not exist Removed
3 WITH READ ONLY Clause not supported, removed

DELETE Statement

Converting deleting rows:

Oracle MariaDB
1 DELETE [FROM] table Delete rows DELETE FROM table FROM keyword is required

Sequences

Note that sequences are available since MariaDB 10.3

CREATE SEQUENCE and ALTER SEQUENCE statements:

Oracle MariaDB
1 CREATE SEQUENCE seqname CREATE [OR REPLACE] SEQUENCE seqname
2 ALTER SEQUENCE seqname ALTER SEQUENCE [IF EXISTS] seqname
3 INCREMENT BY num Positive or negative increment, default is 1 INCREMENT BY num
4 START WITH num Initial value START WITH num
5 MAXVALUE num Maximum value is num MAXVALUE num
NOMAXVALUE System limit NOMAXVALUE
6 MINVALUE num Minimum value is num MINVALUE num
NOMINVALUE System limit NOMINVALUE
7 CYCLE Reuse values after reaching the limit CYCLE
NOCYCLE No reuse, this is default NOCYCLE
8 CACHE num Cache num values, default is 20 CACHE num Default is 1000
NOCACHE Values are not preallocated NOCACHE
9 ORDER Guarantee numbers in order of requests Option not supported, commented
NOORDER No guarantee, this is default Option not supported, removed as it is default
10 NOKEEP Do not keep value during replay Option not supported, removed as it is default
11 NOSCALE Disable sequence scalability Option not supported, removed as it is default
12 GLOBAL A regular sequence visible to all sessions Option not supported, removed as it is default

Referencing sequence values:

Oracle MariaDB
1 seqname.CURRVAL The current value of seqname NEXTVAL(seqname) NEXT VALUE FOR seqname
2 seqname.NEXTVAL The next value of seqname LASTVAL(seqname) PREVIOUS VALUE FOR seqname

Note that in Oracle mode (sql_mode = oracle), you can use the Oracle syntax seqname.CURRVAL and seqname.NEXTVAL to get sequence values in MariaDB.

DROP SEQUENCE statement:

Oracle MariaDB
1 DROP SEQUENCE seqname DROP SEQUENCE [IF EXISTS] seqname

CREATE INDEX Statement

Converting CREATE INDEX statement keywords and clauses:

Oracle MariaDB
1 CREATE INDEX schema.index_name CREATE INDEX index_name Can contain index name only

CREATE TRIGGER Statement

Converting triggers:

Oracle MariaDB
1 CREATE OR REPLACE TRIGGER name DELIMITER //
CREATE OR REPLACE TRIGGER name
2 BEFORE | AFTER | INSTEAD OF BEFORE | AFTER
3 INSERT OR UPDATE OR DELETE Multiple events in one trigger Only one event in trigger
4 ON table ON table
5 REFERENCING OLD AS old_name NEW AS new_name Clause is not supported, OLD and NEW used
6 :NEW.col, :OLD.col Referencing column values NEW.col, OLD.col
7 FOR EACH ROW Row-level trigger FOR EACH ROW
8 BEGIN stmt; … END; / BEGIN stmt; … END; // DELIMITER ;

For more information, see Conversion of PL/SQL Statements.

PL/SQL Statements

Converting variable declaration and assignments:

Oracle MariaDB
1 variable datatype := value Variable declaration DECLARE variable datatype DEFAULT value
2 variable := value Assignment statement SET variable = value

Executing stored procedures from a PL/SQL block:

Oracle MariaDB
1 sp_name(param1, ...) Execute procedure CALL sp_name(param1, ...)
sp_name Procedure without parameters CALL sp_name

Error handling:

Oracle MariaDB
1 RAISE_APPLICATION_ERROR(-20xxx, text) Raise user error SIGNAL SQLSTATE '45xxx' SET MESSAGE_TEXT text

SQL Statements

SQL statements and clauses:

Oracle MariaDB
1 COMMENT ON COLUMN table.column IS 'string' Not supported , should be moved to COMMENT in CREATE TABLE
2 COMMENT ON TABLE table IS 'string' ALTER TABLE table COMMENT 'string'