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:

set session sql_mode='oracle';

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

-t=mariadb_ora

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.

Migration Reference

SQL Language Elements

Converting SQL language elements:

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

Oracle DATE arithmetic:

Oracle MariaDB - Oracle Compatibility Mode
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 - Oracle Compatibility Mode
1 (start1, end1) OVERLAPS (start2, end2) Check if two datetime
intervals overlap
(start2 < end1 AND end2 > start1)

Data Types

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:

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

Other data types:

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

Built-in SQL Functions

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:

Oracle MariaDB - Oracle Compatibility Mode
1 DECODE(exp, when, then, …) Evaluate conditions DECODE(exp, when, then, …)

Converting NULL processing functions:

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

Converting math functions:

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

Converting other functions:

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

CREATE TABLE Statement

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

SELECT Statement

Converting SQL SELECT statement and its clauses:

Oracle MariaDB - Oracle Compatibility Mode
1 select INTERSECT select2 Returns rows that exist in both queries select INTERSECT select2 Since 10.3.0

CREATE VIEW Statement

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

Sequences

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:

Oracle MariaDB - Oracle Compatibility Mode
1 seqname.CURRVAL The current value of seqname seqname.CURRVAL
2 seqname.NEXTVAL The next value of seqname seqname.NEXTVAL

DROP SEQUENCE statement:

Oracle MariaDB - Oracle Compatibility Mode
1 DROP SEQUENCE seqname DROP SEQUENCE [IF EXISTS] seqname

CREATE INDEX 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

SELECT Statement

Converting SQL SELECT statement and its clauses:

Oracle MariaDB - Oracle Compatibility Mode
1 t1 JOIN t2 USING (column_name) USING clause for joins t1 JOIN t2 USING (column_name)
2 CONNECT BY PRIOR Hierarchical queries Recursive Common Table Expressions (CTE)

Rownum pseudo-column conditions:

Oracle MariaDB Oracle mode - Before 10.6.1 MariaDB Oracle mode - Since 10.6.1
1 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

INSERT Statement

Converting SQL INSERT statement and its clauses:

Oracle MariaDB - Oracle Compatibility Mode
1 INSERT INTO table
VALUES rowtype_var
Insert a record from
%ROWTYPE variable
INSERT INTO table VALUES
(rowtype_var.col1, rowtype_var.col2, …)
Columns have to be
explicitly defined

CREATE FUNCTION Statement

Converting user-defined functions:

Oracle MariaDB - Oracle Compatibility Mode
1 CREATE OR REPLACE FUNCTION name DELIMITER //
CREATE OR REPLACE FUNCTION name
2 param IN | OUT | IN OUT datatype Parameter definition param datatype Length can be skipped,
it is derived from the caller
3 RETURN datatype Return value RETURN datatype RETURNS not allowed

CREATE PROCEDURE Statement

Converting stored procedures:

Oracle MariaDB - Oracle Compatibility Mode
1 CREATE OR REPLACE PROCEDURE name DELIMITER //
CREATE OR REPLACE PROCEDURE name

CREATE TRIGGER Statement

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.

PL/SQL Statements

Converting PL/SQL statements:

Oracle MariaDB - Oracle Compatibility Mode
1 CURSOR cur (param IN type, … ) IS … Cursor declaration CURSOR cur (param type, … ) IS … IN not allowed

Flow of control statements:

Oracle MariaDB - Oracle Compatibility Mode
1 RETURN; Return from a stored procedure RETURN;
2 RETURN exp; Return from a user-defined function RETURN exp;

Other statements:

Oracle MariaDB - Oracle Compatibility Mode
1 NULL; “no-op” (no operation) NULL;

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 - Oracle Compatibility Mode
1 RAISE_APPLICATION_ERROR(-20xxx, text) Raise user error SIGNAL SQLSTATE '45xxx' SET MESSAGE_TEXT text

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

SQL Statements

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