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, see Oracle to MariaDB Migration - Compatibility Mode.
Max length:
Oracle | MariaDB | |
Tables, columns, indexes, views, procedures, triggers | 128 (was 30 before Oracle 12c) | 64 |
Converting SQL language elements:
Oracle | MariaDB | |||
1 | -- comment | Single line comment | -- comment | A blank is required after -- |
Oracle DATE arithmetic:
Oracle | MariaDB | |||
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 | |||
1 | (start1, end1) OVERLAPS (start2, end2) | Check if two datetime intervals overlap | (start2 < end1 AND end2 > start1) |
Character data types:
Oracle | MariaDB | |||
1 | CLOB | Character large object, 4 GB | LONGTEXT | |
2 | LONG | Character data, 2 GB | LONGTEXT | |
3 | VARCHAR(n) | Synonym for VARCHAR2 | VARCHAR(n) | |
4 | VARCHAR2(n) | Variable-length string, 1 ⇐ n ⇐ 32,767 | VARCHAR(n) | 0 ⇐ n ⇐ 65,535 |
Unicode character data types:
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 |
Binary data types:
1 | BLOB | Binary large object, ⇐ 4G | LONGBLOB | |
2 | RAW(n) | Variable-length binary data, 1 ⇐ n ⇐ 2000 | VARBINARY(n) | |
3 | LONG RAW | Binary data, < 2G | LONGBLOB |
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(string, pos, len) | Get a substring of string | SUBSTR(string, pos, 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 | TO_DATE(string, format) | Convert string to datetime | STR_TO_DATE(string, format) | |
TO_DATE(string) | CAST(string AS DATETIME) |
Converting NULL processing functions:
Oracle | MariaDB | |||
1 | NVL(exp, replacement) | Replace NULL with the specified value | NVL(exp, replacement) |
Converting math functions:
Oracle | MariaDB | |||
1 | POWER(value, n) | Raise value to the nth power | POWER(value, n) |
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 | CONNECT BY PRIOR | Hierarchical queries | Recursive Common Table Expressions (CTE) | |
4 | select INTERSECT select2 | Select rows existing in both | select INTERSECT select2 | Since 10.3.0 |
5 | ORDER BY col NULLS FIRST | LAST | Order of NULLs | ORDER BY col |
Row limitation:
Oracle | MariaDB - Before 10.6.1 | MariaDB - Since 10.6.1 | ||
1 | OFFSET k FETCH FIRST n ROWS ONLY | Row limiting | LIMIT k, n | |
2 | 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 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 | ENABLE constraint attribute | Removed |
Storage and physical attributes:
Oracle | MariaDB | |
1 | PCTFREE num | Removed |
2 | PCTUSED num | Removed |
3 | INITRANS num | Removed |
4 | MAXTRANS num | Removed |
5 | COMPRESS [BASIC] | COMPRESS num | NOCOMPRESS | Removed |
6 | LOGGING | NOLOGGING | Removed |
7 | SEGMENT CREATION IMMEDIATE | DEFERRED | Removed |
8 | TABLESPACE tablespace_name | Removed |
9 | LOB (column) STORE AS BASIC FILE (params) | Removed |
10 | PARALLEL num | PARALLEL (DEGREE n INSTANCES k) | Removed |
11 | INMEMORY | NO INMEMORY | Removed |
STORAGE clause:
Oracle | MariaDB | |
1 | INITIAL num | Removed |
2 | NEXT num | Removed |
3 | MINEXTENTS num | Removed |
4 | MAXEXTENTS num | Removed |
5 | PCTINCREASE num | Removed |
6 | FREELISTS num | Removed |
7 | FREELIST GROUPS num | Removed |
8 | BUFFER_POOL DEFAULT | KEEP | RECYCLE | Removed |
9 | FLASH_CACHE DEFAULT | KEEP | NONE | Removed |
10 | CELL_FLASH_CACHE DEFAULT | KEEP | NONE | Removed |
LOB storage clause:
Oracle | MariaDB | |
1 | TABLESPACE name | Removed |
2 | DISABLE | ENABLE STORAGE IN ROW | Removed |
3 | CHUNK num | Removed |
4 | NOCACHE | Removed |
5 | LOGGING | Removed |
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:
Oracle | MariaDB | |||
1 | seqname.CURRVAL | The current value of seqname | NEXTVAL(seqname) | NEXT VALUE FOR seqname |
2 | seqname.NEXTVAL | The next value of seqname | LASTVAL(seqname) | PREVIOUS VALUE FOR seqname |
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:
Oracle | MariaDB | |||
1 | DROP SEQUENCE seqname | DROP SEQUENCE [IF EXISTS] seqname |
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 triggers:
For more information, see Conversion of PL/SQL Statements.
Converting variable declaration and assignments:
Oracle | MariaDB | ||
1 | variable datatype := value | Variable declaration | DECLARE variable datatype DEFAULT value |
2 | variable := value | Assignment statement | SET variable = value |
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 | ||
1 | RAISE_APPLICATION_ERROR(-20xxx, text) | Raise user error | SIGNAL SQLSTATE '45xxx' SET MESSAGE_TEXT text |