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 SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
• SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

• Oracle 23, 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.

• Identifiers
• SQL Language Elements
• Data Types
• Built-in SQL Functions
• SELECT
• CREATE TABLE
• CREATE VIEW
• DELETE
• Sequences
• CREATE INDEX
• CREATE FUNCTION
• CREATE TRIGGER
• CREATE SYNONYM
• PL/SQL Statements
• SQL Statements
• Built-in PL/SQL Packages

Max length:

Converting SQL language elements:

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

Operators and predicates:

	Oracle		MariaDB
1	+ and - Operators for datetime	Datetime arithmetic	Interval expressions and functions
2	(start1, end1) OVERLAPS (start2, end2)	Check if datetime intervals overlap	(start2 < end1 AND end2 > start1)
3	exp IS JSON	Check if JSON is valid	JSON_VALID(exp)

Character data types:

	Oracle		MariaDB
1	CLOB	Character large object, 4 GB	LONGTEXT
2	LONG	Character data, 2 GB	LONGTEXT
3	NCHAR(n)	Fixed-length Unicode string, n <= 2000	NCHAR(n)	n <= 255
			NVARCHAR(n)	n > 255
4	NCLOB	Unicode large character object	LONGTEXT
5	NVARCHAR2(n)	Variable-length Unicode string, 1 ⇐ n ⇐ 32,767	NVARCHAR(n)	0 ⇐ n ⇐ 65,535
6	VARCHAR(n)	Synonym for VARCHAR2	VARCHAR(n)
7	VARCHAR2(n)	Variable-length string, 1 ⇐ n ⇐ 32,767	VARCHAR(n)	0 ⇐ n ⇐ 65,535

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
2	TIMESTAMP(p)	Date and time with fraction, 0 ⇐ p ⇐ 9, default is 6	DATETIME(p), 0 ⇐ p ⇐ 6

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	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(str, start, len)	Get substring	SUBSTR(str, start, 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	SYSTIMESTAMP	Get current datetime	NOW(6)
5	TO_DATE(string, format)	Convert string to datetime	STR_TO_DATE(string, format)
	TO_DATE(string)		CAST(string AS DATETIME)

Numeric functions:

Converting NULL processing functions:

Aggregation and window functions:

	Oracle		MariaDB
1	DENSE_RANK() OVER (...)	Rank without gaps	DENSE_RANK() OVER (...)

Converting math functions:

Converting LOB functions:

Converting system functions:

	Oracle		MariaDB
1	SYS_CONTEXT('USERENV', 'OS_USER')	Get OS user	USER()
2	SYS_CONTEXT('USERENV', 'SESSION_USER')	Get current user	USER()
3	USER	Get current user	USER()

Converting other functions:

	Oracle		MariaDB
1	SYS_GUID()	Generate UUID	SYS_GUID()	UUID()

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	CROSS JOIN	Cartesian product	CROSS JOIN
4	CONNECT BY PRIOR	Hierarchical queries	Recursive Common Table Expressions (CTE)
5	select INTERSECT select2	Select rows existing in both	select INTERSECT select2	Since 10.3.0
6	ORDER BY col NULLS FIRST | LAST	Order of NULLs	ORDER BY col

Row limitation:

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	DEFAULT ON NULL exp	Default when NULL is inserted	DEFAULT exp
4	ENABLE constraint attribute		Removed
5	col AS (exp)	Computed column	type col AS (exp)	Data type must be specified

Temporary tables:

	Oracle		MariaDB
1	CREATE GLOBAL TEMPORARY TABLE	Global temporary table	CREATE TEMPORARY TABLE
2	schema.name	Qualified object name	db_name.name

Partitioned tables:

	Oracle		MariaDB
1	PARTITION BY RANGE	Range partitions	PARTITION BY RANGE
2	PARTITION BY LIST	List partitions	PARTITION BY LIST

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

Converting deleting rows:

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

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 indexes:

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

Converting user-defined functions:

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

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.

Converting synonyms:

	Oracle		MariaDB
1	CREATE OR REPLACE SYNONYM name		CREATE OR REPLACE VIEW name	Synonyms are not supported

Converting variable declaration and assignments:

Flow-of-control statements:

	Oracle		MariaDB
1	GOTO label	Jump to label	LEAVE label and BEGIN-END block

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
	sp_name(param1 => value1, ...)	Procedure with named parameters	sp_name(value1, ...)

Error handling:

Table manipulation statements:

	Oracle		MariaDB
1	ALTER TABLE ... ADD FOREIGN KEY	Add foreign key	ALTER TABLE ... ADD FOREIGN KEY Data type issues
2	ALTER TABLE ... ADD UNIQUE ... USING INDEX	Add unique constraint with existing index	Index commented
3	ALTER TABLE ... MODIFY (coldef, ...)	Modify column(s)	ALTER TABLE ... MODIFY coldef, ...

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'

System and Control statements:

	Oracle		MariaDB
1	ALTER USER	Alter user	ALTER USER
2	CREATE USER	Create user	CREATE USER
3	GRANT	Grant privileges	GRANT

Converting built-in PL/SQL packages: