Select Hello World FromDual with MariaDB PL/SQL

MariaDB 10.3 was released GA a few weeks ago. One of the features which interests me most is the MariaDB Oracle PL/SQL compatibility mode.

So its time to try it out now...

Enabling Oracle PL/SQL in MariaDB

Oracle PL/SQL syntax is quite different from old MySQL/MariaDB SQL/PSM syntax. So the old MariaDB parser would through some errors without modification. The activation of the modification of the MariaDB PL/SQL parser is achieved by changing the sql_mode as follows:

mariadb> SET SESSION sql_mode=ORACLE;

or you can make this setting persistent in your my.cnf MariaDB configuration file:

[mysqld]

sql_mode = ORACLE

To verify if the sql_mode is already set you can use the following statement:

mariadb> pager grep --color -i oracle
PAGER set to 'grep --color -i oracle'
mariadb> SELECT @@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 |
mariadb> nopager

Nomen est omen

First of all I tried the function of the basic and fundamental table in Oracle, the DUAL table:

mariadb> SELECT * FROM dual;
ERROR 1096 (HY000): No tables used

Sad. :-( But this query on the dual table seems to work:

mariadb> SELECT 'Hello World!' FROM dual;
+--------------+
| Hello World! |
+--------------+
| Hello World! |
+--------------+

The second result looks much better. The first query should work as well but does not. We opened a bug at MariaDB without much hope that this bug will be fixed soon...

To get more info why MariaDB behaves like this I tried to investigate a bit more:

mariadb> SELECT table_schema, table_name
  FROM information_schema.tables
 WHERE table_name = 'dual';
Empty set (0.001 sec)

Hmmm. It seems to be implemented not as a real table... But normal usage of this table seems to work:

mariadb> SELECT CURRENT_TIMESTAMP() FROM dual;
+---------------------+
| current_timestamp() |
+---------------------+
| 2018-06-07 15:32:11 |
+---------------------+

If you rely heavily in your code on the dual table you can create it yourself. It is defined as follows:

"The DUAL table has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X."

If you want to create the dual table yourself here is the statement:

mariadb> CREATE TABLE `DUAL` (DUMMY VARCHAR2(1));
mariadb> INSERT INTO `DUAL` (DUMMY) VALUES ('X');

Anonymous PL/SQL block in MariaDB

To try some PL/SQL features out or to run a sequence of PL/SQL commands you can use anonymous blocks. Unfortunately MySQL SQL/PSM style delimiter seems still to be necessary.

It is recommended to use the DELIMITER /, then most of the Oracle examples will work straight out of the box...

DELIMITER /

BEGIN
  SELECT 'Hello world from MariaDB anonymous PL/SQL block!';
END;
/

DELIMITER ;

+--------------------------------------------------+
| Hello world from MariaDB anonymous PL/SQL block! |
+--------------------------------------------------+
| Hello world from MariaDB anonymous PL/SQL block! |
+--------------------------------------------------+

A simple PL/SQL style MariaDB Procedure

DELIMITER /

CREATE OR REPLACE PROCEDURE hello AS
BEGIN
  DECLARE
    vString VARCHAR2(255) := NULL;
  BEGIN
    SELECT 'Hello world from MariaDB PL/SQL Procedure!' INTO vString FROM dual;
    SELECT vString;
  END;
END hello;
/

BEGIN
  hello();
END;
/

DELIMITER ;

A simple PL/SQL style MariaDB Function

DELIMITER /

CREATE OR REPLACE FUNCTION hello RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
  DECLARE
    vString VARCHAR2(255) := NULL;
  BEGIN
    SELECT 'Hello world from MariaDB PL/SQL Function!' INTO vString FROM dual;
    RETURN vString;
  END;
END hello;
/

DECLARE
  vString VARCHAR(255) := NULL;
BEGIN
  vString := hello();
  SELECT vString;
END;
/

DELIMITER ;

An PL/SQL package in MariaDB

Up to here there is nothing really new, just slightly different. But now let us try a PL/SQL package in MariaDB:

DELIMITER /

CREATE OR REPLACE PACKAGE hello AS
  -- must be delared as public!
  PROCEDURE helloWorldProcedure(pString VARCHAR2);
  FUNCTION helloWorldFunction(pString VARCHAR2) RETURN VARCHAR2;
END hello;
/

CREATE OR REPLACE PACKAGE BODY hello AS

  vString VARCHAR2(255) := NULL;

  -- was declared public in PACKAGE
  PROCEDURE helloWorldProcedure(pString VARCHAR2) AS
  BEGIN
    SELECT 'Hello world from MariaDB Package Procedure in ' || pString || '!' INTO vString FROM dual;
    SELECT vString;
  END;

  -- was declared public in PACKAGE
  FUNCTION helloWorldFunction(pString VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    SELECT 'Hello world from MariaDB Package Function in ' || pString || '!' INTO vString FROM dual;
    return vString;
  END;
BEGIN
  SELECT 'Package initialiser, called only once per connection!';
END hello;
/

DECLARE
  vString VARCHAR2(255) := NULL;
  -- CONSTANT seems to be not supported yet by MariaDB
  -- cString CONSTANT VARCHAR2(255) := 'anonymous block';
  cString VARCHAR2(255) := 'anonymous block';
BEGIN
  CALL hello.helloWorldProcedure(cString);
  SELECT hello.helloWorldFunction(cString) INTO vString;
  SELECT vString;
END;
/

DELIMITER ;

DBMS_OUTPUT package for MariaDB

An Oracle database contains over 200 PL/SQL packages. One of the most common one is the DBMS_OUTPUT package. In this package we can find the Procedure PUT_LINE.

This package/function has not been implemented yet by MariaDB so far. So we have to do it ourself:

DELIMITER /

CREATE OR REPLACE PACKAGE DBMS_OUTPUT AS
  PROCEDURE PUT_LINE(pString IN VARCHAR2);
END DBMS_OUTPUT;
/

CREATE OR REPLACE PACKAGE BODY DBMS_OUTPUT AS

  PROCEDURE PUT_LINE(pString IN VARCHAR2) AS
  BEGIN
    SELECT pString;
  END;
END DBMS_OUTPUT;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello world from MariaDB DBMS_OUTPUT.PUT_LINE!');
END;
/

DELIMITER ;

The other Functions and Procedures have to be implemented later over time...

Now we can try to do all examples from Oracle sources!