WEBINAR
Technical Guide for Migrating from Oracle to MariaDB
MariaDB's SQL_MODE = ORACLE setting enables compatibility with Oracle Database SQL syntax and behavior in MariaDB. This feature is particularly useful for organizations looking to migrate applications from Oracle Database to MariaDB while preserving the behavior and syntax of Oracle SQL. By setting the to ORACLE, developers can ensure that their existing SQL scripts, application logic, and database interactions are compatible with MariaDB's behavior, easing the migration process. This page provides detailed information on supported Oracle SQL syntax, behavior differences between Oracle and MariaDB, and tips for adapting applications and scripts to work smoothly under this mode.
All traditional MariaDB SQL/PSM syntax should work as before, as long as it does not conflict with Oracle's PL/SQL syntax. All MariaDB functions should be supported in both normal and Oracle modes.
SET SQL_MODE=ORACLE is the same as:
Oracle mode makes the following changes to and :
Oracle mode makes the following changes to :
Oracle mode makes the following changes to :
Oracle mode makes the following changes to :
This was implemented as part of .
If one does a in ORACLE mode on a table that has a native MariaDB DATE column, it will be displayed as to not conflict with the Oracle DATE type.
The following syntax has been supported since :
Oracle mode makes the following changes to :
NULL can be used as a statement:
In Oracle, empty string ('') and NULL are the same thing,
By using sql_mode=EMPTY_STRING_IS_NULL you can get a similar
experience in MariaDB:
and ignore NULL in Oracle mode. Can also be accessed outside of ORACLE mode by using CONCAT_OPERATOR_ORACLE. and .
There are a number of in Oracle mode.
The statement will not display MariaDB-specific table options, such as AUTO_INCREMENT or CHARSET, when Oracle mode is set.
EMPTY_STRING_IS_NULL
SET SQL_MODE='ORACLE';SET SQL_MODE='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT';CREATE FUNCTION f1(a VARCHAR)
can be used without length for routine parameters and RETURN clause. The length is inherited from the argument at call time.
CREATE AGGREGATE FUNCTION f1( )
Creates an , which performs the function against a set of rows and returns one aggregate result.
No CALL needed in Stored Procedures
In Oracle mode one can call other stored procedures with name only.
RETURN. Can also be used in stored procedures
ANSI uses RETURNS. MariaDB mode only supports RETURNS in stored functions
CURSOR c1 IS SELECT a,b FROM t1; rec1 c1%ROWTYPE;
Take ROW structure from a cursor.
Variables can be declared after cursor declarations
In MariaDB mode, variables must be declared before cursors.
Triggers uses :NEW and :OLD
ANSI uses NEW and OLD.
SQLCODE
Returns the number code of the most recent exception. Can only be used in Stored Procedures.
SQLERRM
Returns the error message associdated to it's error number argument or SQLCODE if no argument is given. Can only be used in Stored Procedures.
SQL%ROWCOUNT
Almost same as .
Returns number of accepted rows. From .
Associative arrays
See . From .
, and all have the same precedence.
10.3
has higher precedence than and in non-Oracle modes.
MINUS
10.6
MINUS is a synonym for .
10.3
Returns a VARCHAR(1) with character set and collation according to @@character_set_database and @@collation_database
substr('abc',0 ,3) same as substr('abc', 1 ,3)
10.3
Position 0 for is same as position 1
10.6.1
Generates a globally unique identifier. Similar to but without the -. All modes.
10.6.1
Added to enhance Oracle compatibility. All modes.
12.2.1
Added to enhance Oracle compatibility. All modes.
, , , and
10.3
Returns NULL instead of an empty string if returning an empty result. These functions can also be accessed outside of ORACLE mode by suffixing _ORACLE onto the end of the function name, such as TRIM_ORACLE.
BLOB
CREATE PROCEDURE p1 (param OUT INT)
ANSI uses (OUT param INT)
CREATE PROCEDURE p1 (a IN OUT INT)
ANSI uses (INOUT param INT)
AS before function body
CREATE FUNCTION f1 RETURN NUMBER AS BEGIN...
IS before function body
CREATE FUNCTION f1 RETURN NUMBER IS BEGIN...
If function has no parameters then parentheses must be omitted
Example: CREATE PROCEDURE p1 AS BEGIN NULL; END;
CREATE PROCEDURE p1 AS BEGIN END p1;
Optional routine name after END keyword. MDEV-12089
CREATE PROCEDURE p1 AS CURSOR cur IS (SELECT a, b FROM t1); BEGIN FOR rec IN cur ...
Explicit cursor with . MDEV-10581
CREATE PROCEDURE p1 AS rec IN (SELECT a, b FROM t1)
Implicit cursor with . MDEV-12098
CURSOR c(prm_a VARCHAR2, prm_b VARCHAR2) ... OPEN c(1,2)
Cursor with parameters. MDEV-10597
CURSOR c(prm_a VARCHAR2, prm_b VARCHAR2) ... FOR rec in c(1,2)
Cursor with parameters and . MDEV-12314
s %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
Explicit cursor attributes. MDEV-10582
FOR i IN 1..10 LOOP ... END LOOP
Numeric FOR loop. MDEV-10580
GOTO
<<label>> used with GOTO
ANSI uses label:. MDEV-10697
To leave loop block: EXIT [ label ] [ WHEN bool_expr ]
ANSI syntax is IF bool_expr THEN LEAVE label
[<<label>>] WHILE boolean_expression LOOP statement... END LOOP [ label ] ;
Oracle style WHILE loop
CONTINUE [ label ] [ WHEN boolean_expression]
CONTINUE is only valid inside a loop
var:= 10; Can also be used with MariaDB system variables
MariaDB uses SET var= 10;
var INT := 10
Default variable value
var1 table_name.column_name%TYPE
Take data type from a table column. MDEV-10577
var2 var1%TYPE
Take data type from another variable
rec1 table_name%ROWTYPE
Take ROW structure from a table. MDEV-12133
rec2 rec1%ROWTYPE
Take ROW structure from ROW variable
BEGIN ... EXCEPTION WHEN OTHERS THEN BEGIN .. END; END;
Exception handlers are declared at the end of a block
TOO_MANY_ROWS, NO_DATA_FOUND, DUP_VAL_ON_INDEX
Predefined exceptions. MDEV-10839
RAISE TOO_MANY_ROWS ; .... EXCEPTION WHEN TOO_MANY_ROWS THEN ...
Exception can be used with RAISE and EXCEPTION...WHEN. MDEV-10840
CREATE OR REPLACE FUNCTION f1 (a INT) RETURN INT AS e1 EXCEPTION...
User defined exceptions. MDEV-10587
BEGIN to start a block
MariaDB uses for anyonymous blocks. MDEV-10655
DECLARE is used before BEGIN
DECLARE a INT; b VARCHAR(10); BEGIN v:= 10; END;
WHEN DUP_VAL_ON_INDEX THEN NULL ; NULL; WHEN OTHERS THEN NULL
Do not require BEGIN..END in multi-statement exception handlers in THEN clause. MDEV-12088
ELSIF
10.3
ANSI uses
SELECT UNIQUE
10.3
Same as . MDEV-12086
TRUNCATE TABLE t1 [DROP STORAGE] or [REUSE STORAGE]
10.3
DROP STORAGE and REUSE STORAGE are allowed as optional keywords for . MDEV-10588
without an alias
10.6
10.6.1
Added as a wrapper for to enhance Oracle compatibility. All modes.
10.3
Cast expression to a VARCHAR(N). MDEV-11275
10.3
In Oracle mode, compares and matches search expressions
LENGTH() is same as
10.3
PREPARE stmt FROM 'SELECT :1, :2'
ANSI uses ?. MDEV-10801
EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:x,:y) FROM DUAL' USING 10,20
Dynamic placeholders. MDEV-10801
VARCHAR2
NUMBER(M [,D])
NUMBER
DATE (with time portion)
MariaDB
RAW
CLOB
SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL)
MariaDB translates to . In all modes one can use LENGTHB() as a synonym to OCTET_LENGTH()
IF a=10 THEN NULL; ELSE NULL; END IFSET sql_mode=EMPTY_STRING_IS_NULL;
SELECT '' IS NULL; -- returns TRUE
INSERT INTO t1 VALUES (''); -- inserts NULLWEBINAR
Technical Guide for Migrating from Oracle to MariaDB
This page is licensed: CC BY-SA / Gnu FDL