Oracle to MariaDB Migration

SQLines provides tools to 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 19c, 18c, 12c, 11g, 10g and 9i
• MariaDB 10.x and 5.x

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

• Identifiers
• SQL Language Elements
• Data Types
• Built-in SQL Functions
• SELECT
• Sequences
• CREATE INDEX
• SELECT Statement

Max length:

Converting SQL language elements:

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

Oracle DATE arithmetic:

Operators and predicates:

Character data types:

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:

Date and time data types:

	Oracle		MariaDB
1	DATE	Date and time with seconds	DATETIME

Binary data types:

Other data types:

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

Converting string functions:

	Oracle		MariaDB
1	CHR(num)	Get character from ASCII code	CHR(num)	since 10.3.1
2	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
3	LENGTH(string)	Get length of string in chars	CHAR_LENGTH(string)
4	LPAD(string, len, pad)	Pad the left-side of string	LPAD(string, len, pad)
5	LTRIM(string)	Remove leading spaces	LTRIM(string)
	LTRIM(string, set)	Remove leading chars	TRIM(LEADING set FROM string)
6	MONTHS_BETWEEN(dt1, dt2)	Get difference in months	User-defined function
7	REPLACE(str, search)	Remove search-string	REPLACE(str, search, ")
	REPLACE(str, search, replace)	Replace search-string	REPLACE(str, search, replace)
8	RPAD(string, len, pad)	Pad the right-side of string	RPAD(string, len, pad)
9	RTRIM(string)	Remove trailing spaces	RTRIM(string)
	RTRIM(string, set)	Remove trailing chars	TRIM(TRAILING set FROM string)
10	SUBSTR(string, pos, len)	Get a substring of string	SUBSTR(string, pos, len)
11	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	TO_DATE(string, format)	Convert string to datetime	STR_TO_DATE(string, format)
	TO_DATE(string)		CAST(string AS DATETIME)

Converting NULL processing functions:

Converting math functions:

Converting SQL SELECT statement and its clauses:

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:

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:

Converting CREATE INDEX statement keywords and clauses:

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

Converting SQL SELECT statement and its clauses:

Rownum pseudo-column conditions: