You are here
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!
- Shinguz's blog
- Log in or register to post comments
Comments
Bug about CONSTANT