Oracle SQL_MODE / Compatibility Features, PL/SQL-Like Syntax, and Migration Boundaries

Learning outcomes

A migration team has hundreds of stored procedures written in Oracle-style syntax. Rewriting every statement before any data can move may be wasteful, but setting SQL_MODE=ORACLE globally and declaring victory is equally dangerous. MariaDB Oracle mode changes parser and semantic behavior so selected Oracle constructs work; it does not turn MariaDB into Oracle Database.

1. Observe mode before changing it

SELECT VERSION() AS server_version,       @@SESSION.sql_mode AS session_mode,       @@GLOBAL.sql_mode AS global_mode;-- Keep the experiment session-scoped:SET SESSION sql_mode='ORACLE';SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

Session scope is deliberate. A global change affects future sessions and can alter parser rules, identifier quoting, date/type behavior and stored-program syntax for unrelated applications. Migration labs should be explicit about which connections run in Oracle mode.

2. Show a type mapping that actually changes semantics

DROP DATABASE IF EXISTS advanced21_l3;CREATE DATABASE advanced21_l3;USE advanced21_l3;SET SESSION sql_mode='ORACLE';CREATE TABLE oracle_style_types (  id NUMBER(10) PRIMARY KEY,  event_time DATE,  note VARCHAR2(80));SHOW CREATE TABLE oracle_style_types\G-- MariaDB native DATE can be qualified explicitly inside Oracle mode:CREATE TABLE native_date_demo (  d mariadb_schema.DATE);SHOW CREATE TABLE native_date_demo\G

In Oracle mode, DATE maps to a datetime-like representation to match Oracle expectations. MariaDB provides the mariadb_schema qualifier to request native MariaDB types when names become ambiguous. This is a compatibility feature with real metadata consequences, not cosmetic syntax.

3. Stored-program compatibility: prove the exact construct

SET SESSION sql_mode='ORACLE';DELIMITER /DECLARE  v_count NUMBER := 0;BEGIN  SELECT COUNT(*) INTO v_count FROM oracle_style_types;  SELECT v_count AS rows_seen;END;/DELIMITER ;

Current MariaDB documentation also includes package syntax and Oracle-oriented built-ins. But a stored procedure that parses is only one layer. Oracle jobs, Advanced Queuing, proprietary packages, optimizer hints, data dictionary views, RAC behavior, external procedures, security policies or application assumptions may still require redesign or replacement.

4. Wrong approach: enable Oracle mode globally first

Changing GLOBAL sql_mode before testing all client paths can break native MariaDB applications by changing quoting, type interpretation or stored-routine parsing. The repair is a compatibility inventory plus session/database/application routing strategy. Create dedicated migration test sessions, compile stored objects, run application integration tests, and only then decide whether a scoped or global mode is justified.

5. Build the Oracle→MariaDB compatibility matrix

Compatibility mode should reduce mechanical rewrite work. It should not hide architecture gaps. Every “PASS” needs executable evidence on the target release.

6. Selected semantic probes

SET SESSION sql_mode='ORACLE';SELECT DECODE(2,1,'one',2,'two','other') AS decoded;SELECT LENGTH('abc') AS length_probe;SELECT 'A' || 'B' AS concat_probe;SELECT ROUTINE_SCHEMA,ROUTINE_NAME,SQL_MODEFROM information_schema.ROUTINESWHERE ROUTINE_SCHEMA='advanced21_l3';

Stored programs remember the SQL mode in which they were created. Inspecting metadata helps explain why two routines on the same server can behave differently. Do not assume changing the caller session retroactively changes a stored routine’s creation semantics.

7. Lab cleanup and migration checks

SET SESSION sql_mode=DEFAULT;DROP DATABASE advanced21_l3;SELECT @@SESSION.sql_mode;

Production judgment and bridge to Lesson 4

Use Oracle mode when it measurably reduces migration effort without obscuring long-term ownership. Keep an exit strategy: document which compatibility constructs remain and whether future teams are expected to write Oracle-style or native MariaDB SQL. Lesson 4 examines another seductive abstraction—remote tables that look local—and makes the network, credentials, transaction and failure boundaries explicit.

Oracle mode migration matrix: syntax compatibility, semantic compatibility, and operational compatibility

SQL_MODE=ORACLE should be evaluated feature by feature. A construct can be syntactically accepted yet behave differently in edge cases, data types, exception handling, transaction semantics, package behavior, built-in functions, metadata, or optimizer execution. Create a migration matrix that lists each Oracle dependency, the MariaDB behavior under the exact target version, the test that proves it, and the application rewrite required when compatibility is incomplete.

Keep the compatibility mode scoped deliberately. Session-level experiments are safer than globally changing SQL mode for an existing MariaDB application because SQL mode can change parsing and semantics for unrelated code. Stored programs compiled under one mode also need version-specific testing. Record the mode in schema/migration tooling so a later deployment does not silently compile or execute code under a different contract.

Data mapping deserves separate tests: numeric precision/scale, empty-string versus NULL expectations, date/timestamp semantics, character sets/collations, sequence/identity behavior, LOB handling, and function return types can affect application logic even when procedural syntax looks familiar. Compare representative inputs including boundary values and NULLs.

Operational compatibility includes connectors, bind behavior, migration tools, monitoring, backups, DDL, privileges, and error handling. An Oracle application often relies on ecosystem behavior beyond PL/SQL-like syntax. The correct success criterion is therefore “the tested application contract is supported or redesigned,” not “the stored procedure compiled.”

Porting stored programs: test control flow, exceptions, transactions, and metadata explicitly

Stored-program migration should use behavioral tests, not compilation as the acceptance criterion. For each important routine, build cases for normal return values, NULLs, boundary numeric/date inputs, exception branches, cursor loops, dynamic SQL, transaction boundaries, and side effects. Capture both returned data and modified rows. If Oracle and MariaDB raise different error classes/messages, adapt the application contract rather than hiding the difference behind a blanket compatibility claim.

Inventory Oracle packages, synonyms, sequences, autonomous-transaction expectations, scheduler jobs, database links, proprietary types/functions, and client/session assumptions. Map each to: directly supported and tested; supported with MariaDB-specific rewrite; moved to application/integration code; or unsupported and requiring architecture change. This matrix prevents the easy 80% of PL/SQL-like syntax from concealing the operationally important 20%.

Performance must be retested too. Equivalent procedural syntax can call a different optimizer, storage engine, locking model, and execution runtime. Measure the SQL issued inside routines and inspect its plans/locks rather than benchmarking only the wrapper procedure call. Oracle mode reduces some porting friction; it does not import Oracle's cost model, storage architecture, RAC semantics, ecosystem, or operational tooling.

Migration readiness review for Oracle-dependent applications

Before cutover, review external dependencies that SQL mode cannot emulate: Oracle-specific client drivers, connection/session initialization, database links, scheduler integration, monitoring/backup procedures, RAC/failover assumptions, privilege models, and operational scripts. For each dependency choose a MariaDB-native replacement, an application rewrite, or a retained external service. Include those changes in performance and disaster-recovery testing so “Oracle mode works” does not hide an unported operational dependency.

Also compare error handling. Applications sometimes branch on Oracle error numbers or exception text; MariaDB will expose different SQLSTATE/error codes for many situations. Translate those into application-level categories—retryable conflict, constraint failure, authentication failure, unavailable dependency—rather than attempting a brittle one-for-one string mapping.

Portability boundary after migration

Once the application runs on MariaDB, decide whether Oracle mode remains a permanent contract or only a transition aid. If long-term portability and maintainability matter, schedule removal of compatibility-only syntax where it adds confusion, replace undocumented assumptions with MariaDB-native tests, and record any intentional Oracle-like behavior that must remain. That prevents future developers from mistaking compatibility syntax for proof that the original Oracle semantics still apply everywhere.

Authoritative references

• MariaDB Documentation — SQL_MODE=ORACLE
• MariaDB Documentation — SQL_MODE
• MariaDB Documentation — mariadb_schema
• MariaDB Documentation — CREATE PACKAGE