MariaDB Oracle Mode: Enable SQL_MODE=ORACLE and Understand Its Limits

MariaDB Oracle mode is a SQL compatibility mode, not a complete Oracle Database replacement. It changes SQL parsing and behavior, supports a documented subset of Oracle PL/SQL syntax, and adds Oracle-style sequence syntax. Test your schema, routines, and application against the exact MariaDB version you plan to run. See MariaDB’s SQL_MODE=ORACLE reference.

Enable Oracle mode for a test session

First inspect the active session modes, then add ORACLE without discarding other modes you need, such as strict mode:

SELECT @@SESSION.sql_mode;

SET SESSION sql_mode =
  CONCAT_WS(',', NULLIF(@@SESSION.sql_mode, ''), 'ORACLE');

SELECT @@SESSION.sql_mode;

SET SESSION affects only the current connection. Reapply the mode for each test connection. MariaDB 10.3 and later adds a large subset of Oracle PL/SQL syntax; in versions up to 10.2, ORACLE only selects a set of SQL syntax and behavior flags. See the SQL_MODE version notes.

ORACLE also enables other mode flags. For example, ANSI_QUOTES makes double quotes delimit identifiers, and PIPES_AS_CONCAT makes || concatenate strings instead of acting as logical OR. Test existing MariaDB queries that use these tokens before enabling the mode for an application.

Use an Oracle-style sequence

MariaDB sequences are separate database objects. In Oracle mode, access the next sequence value with sequence_name.NEXTVAL:

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;

SELECT order_seq.NEXTVAL FROM DUAL;

This is not the same as replacing every Oracle sequence with AUTO_INCREMENT; sequences can be consumed independently of inserting a row. MariaDB also supports ANSI NEXT VALUE FOR syntax and its own NEXTVAL() form. See the sequence overview and NEXT VALUE FOR reference.

Create a routine with Oracle-style syntax

With Oracle mode active in the session, MariaDB accepts Oracle-style routine syntax such as AS before the body and an optional routine name after END:

DELIMITER /
CREATE OR REPLACE PROCEDURE p1 AS
BEGIN
  NULL;
END p1;
/
DELIMITER ;

MariaDB supports a subset of PL/SQL constructs, including cursor syntax, loops, and packages, with version-specific availability. For example, package syntax is documented from MariaDB 10.3.5. Do not assume every Oracle package, built-in, data dictionary view, or client behavior is compatible.

Stored programs and views use the sql_mode that was active when they were created. Set the intended mode before creating or recreating routines, then verify the stored definition and test its behavior; changing the session mode later does not convert an existing routine. See MariaDB’s SQL_MODE and stored-program rules.

Review Oracle-to-MariaDB type and behavior differences

Oracle mode recognizes several Oracle type names as MariaDB synonyms. Examples include VARCHAR2 as VARCHAR, NUMBER(M,D) as DECIMAL(M,D), an unqualified NUMBER as DOUBLE, a DATE value with a time portion as DATETIME, and RAW as VARBINARY. These mappings do not guarantee identical precision, range, or storage semantics; validate each column definition and its data during migration.

Oracle treats an empty string as NULL, but enabling SQL_MODE=ORACLE alone does not make that change. MariaDB documents a separate EMPTY_STRING_IS_NULL mode for similar behavior. Also, SHOW CREATE TABLE in Oracle mode omits MariaDB-specific table options such as AUTO_INCREMENT and CHARSET, so do not treat its output as a complete migration diff. See MariaDB’s Oracle-mode type mappings and behavior notes and SQL mode reference.

Plan migration in stages

Enable Oracle mode in a test session, compare representative query results, then migrate and test stored routines, sequence use, type mappings, empty-string handling, and application connections separately. Keep a list of unsupported or different behaviors and convert them explicitly; mode compatibility can reduce syntax changes, but it does not prove that an application is portable without modification.

For MariaDB setup and SQL guides, visit the MariaDB tutorial and MariaDB Reference.