The Ultimate Guide to High Availability with MariaDB

SQL_MODE=ORACLE From MariaDB 10.3

You are viewing an old version of this article. View the current version here.

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

  Register Now

From MariaDB 10.3, setting the sql_mode system variable to Oracle causes the server to understand a subset of Oracle's PL/SQL language instead of the traditional MariaDB syntax for stored routines.

Contents

  1. Supported syntax in ORACLE mode
    1. Stored Procedures & Stored functions
    2. Cursors
    3. LOOP
    4. Variables
    5. Exceptions
    6. BEGIN blocks
    7. Simple syntax compatibility
    8. Functions
    9. Prepared statements
    10. Synonymes for basic SQL types
    11. NULL handling
      1. NULL as a statement
      2. Translating empty string literals to NULL
      3. Concat operator ignores NULL
  2. See Also

Supported syntax in ORACLE mode

Stored Procedures & Stored functions

Oracle syntaxDescription
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 bodyCREATE FUNCTION f1 RETURN NUMBER AS BEGIN...
IS before function bodyCREATE FUNCTION f1 RETURN NUMBER IS BEGIN...
If function has no parameters then parentheses must be omittedExample: 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
No CALL# needed in Stored ProceduresIn Oracle mode one can call other stored procdures with name only. MDEV-12107
RETURN. Can also be used in stored proceduresANSI uses RETURNS. MariaD mode only supports RETURNS in stored functions

Cursors

Oracle syntaxDescription
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, %NOTFOUNDExplicit cursor attributes. MDEV-10582

LOOP

Oracle syntaxDescription
FOR i IN 1..10 LOOP ... END LOOPNumeric FOR loop. MDEV-10580
GOTOGOTO statement. MDEV-10697
<<label>> used with GOTOANSI 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 valied inside a loop

Variables

Oracle syntaxDescription
var:= 10; Can also be used with MariaDB systemvariablesMariaDB uses SET var= 10;
var INT := 10Default variable value
var1 table_name.column_name%TYPETake data type from a table column. MDEV-10577
var2 var1%TYPETake data type from another variable
rec1 table_name%ROWTYPETake ROW structure from a table. MDEV-12133
rec2 rec1%ROWTYPETake ROW structure from ROW variable
CURSOR c1 IS SELECT a,b FROM t1; rec1 c1%ROWTYPE;Take ROW structure from a cursor. MDEV-12011
Variables can be declared after cursor declarationsIn MariaDB mode, variables must be declared before cursors. MDEV-10598
Triggers uses :NEW and :OLDANSI uses NEW and OLD. MDEV-10579
SQLCODEReturns the number code of the most recent exception. Can only be used in Stored Procedures. MDEV-10578
SQLERRMReturns 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%ROWCOUNTAlmost same as ROW_COUNT(). MDEV-10583

Exceptions

Oracle syntaxDescription
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_INDEXPredefined 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 syntaxDescription
BEGIN to start a blockMariaDB uses BEGIN NOT ATOMIC for anyonymous blocks. MDEV-10655
DECLARE is used before BEGINDECLARE a INT; b VARCHAR(10); BEGIN v:= 10; END;
WHEN DUP_VAL_ON_INDEX THEN NULL ; NULL; WHEN OTHERS THEN NULLDo not require BEGIN..END in multi-statement exception handlers in THEN clause. MDEV-12088

Simple syntax compatibility

Oracle syntaxDescription
ELSIFANSI uses ELSEIF
SELECT UNIQUESame as SELECT DISTINCT. MDEV-12086
TRUNCATE TABLE t1 [ STORAGE ]STORAGE is allowed as an optional keyword for TRUNCATE TABLE. MDEV-10588

Functions

Oracle syntaxDescription
CAST(expr as VARCHAR(N))Cast expression to a VARCHAR(N). MDEV-11275
LENGTH() is same as CHAR_LENGTH()MariaDB translates LENGTH() to OCTET_LENGTH(). In all modes on can use LENGTHB() as a synonym to OCTET_LENGTH()##
CHAR(num)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)Position 0 for substr() is same as position 1

Prepared statements

Oracle syntaxDescription
PREPARE stmt FROM 'SELECT :1, :2'ANSI uses ?. MDEV-10801
EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:x,:y) FROM DUAL' USING 10,20Dynamic placeholders. MDEV-10801

Synonymes for basic SQL types

Oracle typeMariaDB synonym
VARCHAR2VARCHAR
NUMBERDECIMAL
DATE (with time portion)MariaDB DATETIME
RAWVARBINARY
CLOBLONGTEXT
BLOBLONGBLOB

This was implemented as part of MDEV-10343.

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 || ignores NULL in Oracle mode. MDEV-11880 and MDEV-12143.

See Also

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.