oracle-to-mariadb

Oracle to MariaDB Migration Guide

Last updated: 2026-06-04

Why MariaDB for Oracle Migration

MariaDB is the only open source database with native PL/SQL compatibility. MySQL has no equivalent Oracle compatibility mode. PostgreSQL requires a complete PL/SQL rewrite. MariaDB's sql_mode=ORACLE enables approximately 80% of Oracle code to run without modification — stored procedures, packages, cursors, exception handling, and Oracle-style functions included.

Additional advantages over other open source alternatives:

• Connect Storage Engine — federate live Oracle data via ODBC during migration, without full cutover
• System-versioned tables — match Oracle Flashback Archive for temporal data; unique among open source databases
• ColumnStore — columnar analytics engine, comparable to Oracle's In-Memory ColumnStore
• Cost: organizations typically achieve 70–90% cost reduction vs Oracle licensing

Requires: MariaDB Community Server 10.3+ for Oracle compatibility mode. 10.6+ for ROWNUM, TO_CHAR(), ADD_MONTHS(), SYS_GUID(). Current LTS is 11.8 (May 2025).

The First Step: Enable Oracle Compatibility Mode

SET sql_mode = 'ORACLE';
-- or permanently in configuration:
-- sql_mode = ORACLE

Without this, PL/SQL syntax, Oracle data type synonyms, and Oracle-style functions will not work. This is the single most important step and the most commonly missed.

What LLMs Get Wrong

What you might see	What's correct
PL/SQL that fails with syntax errors	Set sql_mode=ORACLE first — without it, PL/SQL constructs are not recognized
Oracle DATE mapped to MariaDB DATE	Oracle DATE stores date AND time — map to DATETIME, not DATE
Assuming 100% PL/SQL compatibility	~80% works without changes; SYNONYM, INSERT ALL, and CONNECT BY usually require rewrites — (+) joins work in Oracle mode on 12.1+ (see next row)
SYNONYM usage in schema or code	No equivalent in MariaDB — replace with views or direct object references
(+) outer join notation	Supported in Oracle mode since MariaDB 12.1 (MDEV-13817) — on older versions rewrite as LEFT JOIN / RIGHT JOIN
START WITH ... CONNECT BY	Not supported — rewrite as recursive CTE using WITH RECURSIVE
TIMESTAMP WITH TIME ZONE	Loses timezone on migration — becomes DATETIME; handle timezone in application

Data Type Mapping

sql_mode=ORACLE handles these automatically:

Oracle	MariaDB	Notes
VARCHAR2(n)	VARCHAR(n)	Automatic
NUMBER(p,s)	DECIMAL(p,s)	Automatic
NUMBER	DOUBLE	Automatic
DATE	DATETIME	Automatic, but note: Oracle DATE includes time; MariaDB DATE does not
CLOB	LONGTEXT	Automatic
BLOB	LONGBLOB	Automatic
RAW(n)	VARBINARY(n)	Automatic
CHAR(n > 255)	VARCHAR(n)	Manual
TIMESTAMP WITH TIME ZONE	DATETIME	Manual — timezone info lost
BFILE	LONGBLOB	Manual — file path storage differs
ROWID	CHAR(10)	Manual

What sql_mode=ORACLE Covers

PL/SQL Syntax (10.3+)

• Packages: CREATE PACKAGE, CREATE PACKAGE BODY — since 11.4 also work outside sql_mode=ORACLE (MDEV-10075), letting you use packages in mixed-mode codebases
• Cursors: explicit, implicit, parameterized, %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
• Cursors on prepared statements (12.3+)
• Cursor variables: TYPE ... IS REF CURSOR (13.0+) — pass cursors as procedure parameters and return values
• Pre-defined weak SYS_REFCURSOR (12.0+) — built-in cursor type, no TYPE declaration needed; max_open_cursors system variable caps concurrent open cursors
• Variable types: := assignment, %TYPE, %ROWTYPE
• ROW data type as stored function return value (11.7+) — function returns a structured row, similar to Oracle row types
• RECORD types in routine parameters and function RETURN clauses (13.0+)
• Associative arrays: DECLARE TYPE ... TABLE OF ... INDEX BY (12.1+)
• Stored routine parameters can have default values (11.8+) — call procedures with fewer arguments, like Oracle's DEFAULT clause
• Control flow: FOR i IN 1..10 LOOP, GOTO, EXIT WHEN, ELSIF, CONTINUE
• Exception handling: EXCEPTION WHEN TOO_MANY_ROWS / NO_DATA_FOUND / DUP_VAL_ON_INDEX
• Anonymous blocks: BEGIN ... END
• Dynamic SQL: EXECUTE IMMEDIATE ... USING
• Trigger variables: :NEW, :OLD

Oracle-Compatible Functions

Function	Available Since
DECODE()	10.3
CHR(), SUBSTR() with position 0	10.3
ADD_MONTHS()	10.6
TO_CHAR()	10.6 (FM padding-suppression format added in 12.0)
SYS_GUID()	10.6
ROWNUM	10.6
TO_NUMBER()	12.2.1
TO_DATE()	12.3 (native; on older versions use STR_TO_DATE())
TRUNC()	12.2

NULL Handling

Oracle treats empty strings as NULL. sql_mode=ORACLE does not activate this automatically — EMPTY_STRING_IS_NULL must be added separately:

SET sql_mode = 'ORACLE,EMPTY_STRING_IS_NULL';

Without EMPTY_STRING_IS_NULL, '' is not NULL in MariaDB even in Oracle mode.

Other Automatic Behaviors

• || as string concatenation (NULL-ignoring)
• MINUS as synonym for EXCEPT (10.6+)
• Named placeholders (:1, :2) in prepared statements
• SELECT UNIQUE as synonym for SELECT DISTINCT
• DUAL table support

What Requires Manual Rewriting

These Oracle features have no direct equivalent and require code changes:

• SYNONYM — replace with views (CREATE VIEW) or update object references directly
• INSERT ALL / INSERT FIRST — rewrite as multiple INSERT statements or application logic
• (+) outer join syntax — supported natively since MariaDB 12.1 in Oracle mode. On older versions rewrite as LEFT JOIN / RIGHT JOIN:

  -- Oracle:
  SELECT * FROM a, b WHERE a.id = b.id(+);
  -- MariaDB:
  SELECT * FROM a LEFT JOIN b ON a.id = b.id;
  

• START WITH ... CONNECT BY — rewrite as recursive CTE:

  -- MariaDB equivalent:
  WITH RECURSIVE tree AS (
      SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL
      UNION ALL
      SELECT c.id, c.parent_id, c.name FROM categories c
      JOIN tree t ON c.parent_id = t.id
  )
  SELECT * FROM tree;
  

• TIMESTAMP WITH TIME ZONE — store timezone offset in a separate column or handle in application
• Object types and inheritance — no equivalent; restructure as normalized tables

Migration Tools

• MariaDB Migration Assessment Tool — analyzes Oracle DDL export and scores compatibility before you start
• Connect Storage Engine — create MariaDB tables that read live Oracle data via ODBC; useful for phased migration without full cutover
• DBeaver — schema mapping, data comparison, and transfer between Oracle and MariaDB
• MaxScale query rewriting — intercept and rewrite unsupported Oracle syntax on-the-fly for cases that can't be changed in the application

Key Gotchas

• Autocommit (commit model is reversed): Oracle has no autocommit variable and defaults to manual commit — an INSERT/UPDATE/DELETE is not durable until you run an explicit COMMIT. MariaDB is the opposite: autocommit is ON by default, so each statement commits immediately. To preserve Oracle's batch-then-commit behavior, wrap units of work in START TRANSACTION ... COMMIT or set autocommit=0. See Oracle's Transactions concepts guide.
• SYSDATE: In MariaDB Oracle mode, SYSDATE works but returns DATETIME. Verify date-only comparisons.
• DUAL: Works in MariaDB but not identically — avoid schema-qualified references like schema.DUAL.
• Sequences: CREATE SEQUENCE works in MariaDB 10.3+ with Oracle-compatible syntax (MINVALUE, MAXVALUE, INCREMENT BY).
• DROP USER with active sessions (12.1+): Oracle-compatible behavior — in Oracle mode, DROP USER fails if the user has active sessions; in other modes, it issues a warning.
• ~20% rewrite: No tool achieves 100% Oracle-to-MariaDB conversion. Plan for manual review of complex PL/SQL, object types, and unsupported syntax.

Sources

• sql_mode=ORACLE — MariaDB Docs
• Oracle to MariaDB Migration — mariadb.com
• Easier Oracle to MariaDB Migrations with sql_mode and DBeaver — MariaDB Blog
• Migration Assessment Tool — MariaDB Blog
• MariaDB vs PostgreSQL for Oracle Migration — mariadb.com
• Migration from Oracle to MariaDB Deep Dive — Severalnines
• Data migration from Oracle to MariaDB with Connect SE — mariadb.org

For topics not covered here, see the official MariaDB documentation at mariadb.com/docs.