This is an old revision of the document!


Oracle to MariaDB Migration

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 - SQL scripts conversion tool

Databases:

  • Oracle 12c, 11g, 10g and 9i
  • MariaDB 10.x and 5.x

Starting from MariaDB 10.3 there is Oracle compatibility mode, see Oracle to MariaDB Migration - Compatibility Mode.

Migration Reference

Data Types

Date and time data types:

Oracle MariaDB
1 DATE Date and time with seconds DATETIME

Other data types:

1 XMLTYPE XML data LONGTEXT

Built-in SQL Functions

Datetime functions:

Oracle MariaDB
1 TO_DATE(string, format) Convert string to datetime STR_TO_DATE(string, format)
TO_DATE(string) CAST(string AS DATETIME)

Sequences

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

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