Oracle to MariaDB Migration - Oracle Compatibility Mode

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, 21c, 19c, 18c, 12c, 11g, 10g and 9i
• MariaDB 11.x and 10.x

Starting from MariaDB 10.3 there is the Oracle compatibility mode that allows MariaDB to understand a subset of Oracle's PL/SQL language. To enable this mode in MariaDB CLI, use:

To perform the conversion to MariaDB Oracle compatibility mode in SQLines SQL Converter, use:

Note that even in this mode, some language clauses still may be converted to MariaDB native SQL language as the Oracle compatibility mode may not cover all Oracle features.

This reference documents Oracle to MariaDB migration issues in the Oracle compatibility mode. For migration to the MariaDB native SQL language, see Oracle to MariaDB Migration.

• SQL Language Elements
• Data Types
• Built-in SQL Functions
• CREATE TABLE
• CREATE VIEW
• SELECT
• Sequences
• CREATE INDEX
• INSERT Statement
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE TRIGGER
• PL/SQL Statements
• SQL Statements

Converting SQL language elements:

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

Oracle DATE arithmetic:

Operators and predicates:

Character data types:

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 - Oracle Compatibility Mode
1	BINARY_DOUBLE	64-bit floating-point number	DOUBLE
2	BINARY_INTEGER	32-bit signed integer number, PL/SQL only	INTEGER
3	NUMBER(p), NUMBER(p,0)	Integer	NUMBER(p), NUMBER(p,0)
	NUMBER(p,s), NUMBER(*,s)	Fixed-point number	NUMBER(p,s), NUMBER(38,s)
	NUMBER, NUMBER(*)	Exact floating-point number	NUMBER
4	PLS_INTEGER	32-bit signed integer number, PL/SQL only	INTEGER

Date and time data types:

	Oracle		MariaDB - Oracle Compatibility Mode
1	DATE	Date and time with seconds	DATE
2	TIMESTAMP(p)	Date and time with fraction, 0 ⇐ p ⇐ 9, default is 6	TIMESTAMP(p), 0 ⇐ p ⇐ 6

Binary data types:

Other data types:

	Oracle		MariaDB - Oracle Compatibility Mode
1	ROWID	Physical row address	CHAR(10)
2	XMLTYPE	XML data	LONGTEXT

Converting string functions:

	Oracle		MariaDB - Oracle Compatibility Mode
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	LENGTH(string)
5	LPAD(string, len, pad)	Pad the left-side of string	LPAD(string, len, pad)
6	LTRIM(string)	Remove leading spaces	LTRIM(string)
	LTRIM(string, set)	Remove leading chars	TRIM(LEADING set FROM string)
7	MONTHS_BETWEEN(dt1, dt2)	Get difference in months	User-defined function
8	REPLACE(str, search)	Remove search-string	REPLACE(str, search, ")
	REPLACE(str, search, replace)	Replace search-string	REPLACE(str, search, replace)
9	RPAD(string, len, pad)	Pad the right-side of string	RPAD(string, len, pad)
10	RTRIM(string)	Remove trailing spaces	RTRIM(string)
	RTRIM(string, set)	Remove trailing chars	TRIM(TRAILING set FROM string)
11	SUBSTR(string, pos, len)	Get a substring of string	SUBSTR(string, pos, len)
12	TRANSLATE(string, from, to)	Replace characters	Nested REPLACE or User-defined function

Converting date and time functions:

	Oracle		MariaDB - Oracle Compatibility Mode
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	since 10.6.1
			SYSDATE()	Returns time
4	SYSTIMESTAMP	Get current datetime	NOW(6)
5	TO_CHAR(datetime, format)	Convert datetime to string	TO_CHAR(datetime, format)	since 10.6.1
			DATE_FORMAT(datetime, format)
6	TO_DATE(string, format)	Convert string to datetime	STR_TO_DATE(string, format)
7	TRUNC(datetime)	Truncate datetime value	CAST and DATE_FORMAT

Converting case functions:

Converting NULL processing functions:

Converting math functions:

Converting other functions:

	Oracle		MariaDB - Oracle Compatibility Mode
1	SYS_GUID()	Generate UUID	SYS_GUID()	UUID()

Converting CREATE TABLE statement keywords and clauses:

	Oracle		MariaDB - Oracle Compatibility Mode
1	GENERATED AS IDENTITY	Identity column	AUTO_INCREMENT
2	CONSTRAINT name NOT NULL	Named NOT NULL constraint	NOT NULL
3	ENABLE constraint attribute		Removed
4	col AS (exp)	Computed column	type col AS (exp)	Data type must be specified

Temporary tables:

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

Partitioned tables:

	Oracle		MariaDB - Oracle Compatibility Mode
1	PARTITION BY RANGE	Range partitions	PARTITION BY RANGE

Converting SQL SELECT statement and its clauses:

Converting views:

	Oracle		MariaDB - Oracle Compatibility Mode
1	CREATE OR REPLACE VIEW name		CREATE OR REPLACE VIEW name
2	FORCE	Create view even if tables not exist	Keyword removed
3	EDITIONABLE	Editioning view	Keyword removed
4	WITH READ ONLY		Clause not supported, removed

CREATE SEQUENCE and ALTER SEQUENCE statements:

	Oracle		MariaDB - Oracle Compatibility Mode
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:

DROP SEQUENCE statement:

Converting CREATE INDEX statement keywords and clauses:

	Oracle	MariaDB - Oracle Compatibility Mode
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:

Converting SQL INSERT statement and its clauses:

Converting user-defined functions:

Converting stored procedures:

Converting triggers:

	Oracle		MariaDB - Oracle Compatibility Mode
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 PL/SQL statements:

Flow of control statements:

Other statements:

Executing stored procedures from a PL/SQL block:

Error handling:

Predefined exceptions:

	Oracle		MariaDB - Oracle Compatibility Mode
1	ACCESS_INTO_NULL	Assign attribute of NULL object	Not supported
2	CASE_NOT_FOUND	No condition met in CASE	Not supported
3	COLLECTION_IS_NULL	Assigning to NULL collection	Not supported
4	CURSOR_ALREADY_OPEN	Cursor already open	Not supported
5	DUP_VAL_ON_INDEX	Insert duplicate values	DUP_VAL_ON_INDEX
6	INVALID_CURSOR	Operation on non-open cursor	Not supported
7	INVALID_NUMBER	String does not represent valid number	Not supported
8	LOGIN_DENIED	Invalid user name or password	Not supported
9	NO_DATA_FOUND	SELECT INTO returns no rows	NO_DATA_FOUND
10	NO_DATA_NEEDED	Early exit from pipelined function	Not supported
11	NOT_LOGGED_ON	Not connected	Not supported
12	OTHERS	All other conditions	OTHERS
13	PROGRAM_ERROR	Internal error	Not supported
14	ROWTYPE_MISMATCH	Incompatible types	Not supported
15	SELF_IS_NULL	Invoke method of NULL instance	Not supported
16	STORAGE_ERROR	Out of memory	Not supported
17	SUBSCRIPT_BEYOND_COUNT	Index number too large	Not supported
18	SUBSCRIPT_OUTSIDE_LIMIT	Index number is out of range	Not supported
19	SYS_INVALID_ROWID	Invalid rowid	Not supported
20	TIMEOUT_ON_RESOURCE	Timeout	Not supported
21	TOO_MANY_ROWS	SELECT INTO returns more than one row	TOO_MANY_ROWS
22	VALUE_ERROR	Expression error	Not supported
23	ZERO_DIVIDE	Divide a number by zero	Not supported

Table manipulation statements:

	Oracle		MariaDB - Oracle Compatibility Mode
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 - Oracle Compatibility Mode
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 - Oracle Compatibility Mode
1	ALTER USER	Alter user	ALTER USER
2	CREATE USER	Create user	CREATE USER
3	GRANT	Grant privileges	GRANT