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:
Databases:
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.
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) |
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 |
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() ![]() |
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:
Oracle | MariaDB - Oracle Compatibility Mode | |||
1 | select INTERSECT select2 | Returns rows that exist in both queries | select INTERSECT select2 | Since 10.3.0 |
Converting views:
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 |
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:
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 |
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 |
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 |
Converting stored procedures:
Oracle | MariaDB - Oracle Compatibility Mode | |||
1 | CREATE OR REPLACE PROCEDURE name … | DELIMITER // CREATE OR REPLACE PROCEDURE name … |
Converting triggers:
For more information, see Conversion of 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:
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:
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 ![]() |