Oracle to MariaDB Migration - Oracle Compatibility Mode

SQLines open source 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 - Migration assessment and SQL scripts conversion tool

Databases:

  • Oracle 12c, 11g, 10g and 9i
  • MariaDB 10.x and 5.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, run:

set sql_mode=oracle;

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

Views

CREATE VIEW and ALTER VIEW statements:

Oracle MariaDB - Oracle Compatibility Mode
1 CREATE MATERIAZED VIEW name Create a materiazed view Not supported

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

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

PL/SQL Statements

Converting PL/SQL statements:

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

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;

Exception Block

Predefined exceptions handlers:

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

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