SQL_MODE=ORACLE

Live Webinar November 21: Technical Guide for Migrating from Oracle to MariaDB

From MariaDB 10.3, setting the sql_mode system variable to Oracle allows the server to understand a subset of Oracle's PL/SQL language. For example:

SET SQL_MODE='ORACLE';

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.

Prior to MariaDB 10.3, MariaDB does not support Oracle's PL/SQL language, and SET SQL_MODE=ORACLE is only an alias for the following sql_mode in those versions:

SET SQL_MODE='PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS,
NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER';

From MariaDB 10.3, SET SQL_MODE=ORACLE is same as:

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';

Supported Syntax in Oracle Mode
See Also

Supported Syntax in Oracle Mode

Stored Procedures and Stored Functions

Oracle mode makes the following changes to Stored Procedures and Stored Functions:

Oracle syntax	Description
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 FUNCTION f1(a `VARCHAR`)	VARCHAR can be used without length for routine parameters and RETURN clause. The length is inherited from the argument at call time. MDEV-10596
CREATE AGGREGATE FUNCTION f1( )	Creates an aggregate function, 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. MDEV-12107
`RETURN`. Can also be used in stored procedures	ANSI uses `RETURNS`. MariaDB mode only supports RETURNS in stored functions

Cursors

Oracle mode makes the following changes to Cursors:

Oracle syntax	Description
CREATE PROCEDURE p1 AS CURSOR cur IS (SELECT a, b FROM t1); BEGIN FOR rec IN cur ...	Explicit cursor with FOR loop. MDEV-10581
CREATE PROCEDURE p1 AS `rec IN (SELECT a, b FROM t1)`	Implicit cursor with FOR loop. 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 FOR loop. MDEV-12314
s %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND	Explicit cursor attributes. MDEV-10582

LOOP

Oracle mode makes the following changes to LOOP:

Oracle syntax	Description
`FOR i IN 1..10 LOOP ... END LOOP`	Numeric FOR loop. MDEV-10580
`GOTO`	GOTO statement. MDEV-10697
`<<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

Variables

Oracle syntax	Version	Description
`var:= 10`; Can also be used with MariaDB systemvariables	10.3	MariaDB uses `SET var= 10`;
`var INT := 10`	10.3	Default variable value
`var1 table_name.column_name%TYPE`	10.3	Take data type from a table column. MDEV-10577
`var2 var1%TYPE`	10.3	Take data type from another variable
`rec1 table_name%ROWTYPE`	10.3	Take ROW structure from a table. MDEV-12133
`rec2 rec1%ROWTYPE`	10.3	Take ROW structure from ROW variable
CURSOR c1 IS SELECT a,b FROM t1; rec1 c1%ROWTYPE;	10.3	Take ROW structure from a cursor. MDEV-12011
Variables can be declared after cursor declarations	10.3	In MariaDB mode, variables must be declared before cursors. MDEV-10598
Triggers uses `:NEW` and `:OLD`	10.3	ANSI uses `NEW` and `OLD`. MDEV-10579
`SQLCODE`	10.3	Returns the number code of the most recent exception. Can only be used in Stored Procedures. MDEV-10578
`SQLERRM`	10.3	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. MDEV-10578
`SQL%ROWCOUNT`	10.3	Almost same as ROW_COUNT(). MDEV-10583
`ROWNUM`	10.6.1	Returns number of accepted rows

Exceptions

Oracle syntax	Description
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 Blocks

Oracle syntax	Description
`BEGIN` to start a block	MariaDB uses `BEGIN NOT ATOMIC` 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

Simple Syntax Compatibility

Oracle syntax	Version	Description
`ELSIF`	10.3	ANSI uses `ELSEIF`
SELECT `UNIQUE`	10.3	Same as `SELECT DISTINCT`. MDEV-12086
TRUNCATE TABLE t1 [`DROP STORAGE`] or [`REUSE STORAGE`]	10.3	DROP STORAGE and REUSE STORAGE are allowed as optional keywords for TRUNCATE TABLE. MDEV-10588
Subqueries in a FROM clause without an alias	10.6	`SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL)`
UNION, EXCEPT and INTERSECT all have the same precedence.	10.3	INTERSECT has higher precedence than UNION and EXCEPT in non-Oracle modes.
`MINUS`	10.6	`MINUS` is a synonym for EXCEPT.

Functions

Oracle syntax	Version	Description
ADD_MONTHS()	10.6.1	Added as a wrapper for DATE_ADD() to enhance Oracle compatibility. All modes.
CAST(expr `as VARCHAR(N)`)	10.3	Cast expression to a `VARCHAR(N)`. MDEV-11275
DECODE	10.3	In Oracle mode, compares and matches search expressions
`LENGTH()` is same as CHAR_LENGTH()	10.3	MariaDB translates LENGTH() to OCTET_LENGTH(). In all modes one can use `LENGTHB()` as a synonym to `OCTET_LENGTH()`
CHR(num)	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 substr() is same as position 1
SYS_GUID	10.6.1	Generates a globally unique identifier. Similar to UUID but without the `-`. All modes.
TO_CHAR	10.6.1	Added to enhance Oracle compatibility. All modes.
TRIM, LTRIM, RTRIM, LPAD and RPAD	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.

Prepared Statements

Oracle mode makes the following changes to Prepared Statements:

Oracle syntax	Description
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

Synonyms for Basic SQL Types

Oracle type	MariaDB synonym
VARCHAR2	VARCHAR
NUMBER	DECIMAL
DATE (with time portion)	MariaDB DATETIME
RAW	VARBINARY
CLOB	LONGTEXT
BLOB	LONGBLOB

This was implemented as part of MDEV-10343.

If one does a SHOW CREATE TABLE in ORACLE mode on a table that has a native MariaDB DATE column, it will be displayed as mariadb_schema.date to not conflict with the Oracle DATE type.

Packages

The following syntax has been supported since MariaDB 10.3.5:

NULL Handling

Oracle mode makes the following changes to NULL handling:

NULL As a Statement

NULL can be used as a statement:

IF a=10 THEN NULL; ELSE NULL; END IF

Translating Empty String Literals to NULL

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:

SET sql_mode=EMPTY_STRING_IS_NULL;
SELECT '' IS NULL; -- returns TRUE
INSERT INTO t1 VALUES (''); -- inserts NULL

Concat Operator Ignores NULL

CONCAT() and || ignore NULL in Oracle mode. Can also be accessed outside of ORACLE mode by using CONCAT_OPERATOR_ORACLE. MDEV-11880 and MDEV-12143.

Reserved Words

There are a number of extra reserved words in Oracle mode.

SHOW CREATE TABLE

The SHOW CREATE TABLE statement will not display MariaDB-specific table options, such as AUTO_INCREMENT or CHARSET, when Oracle mode is set.

Comments

Comments loading...

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.