EXECUTE IMMEDIATE Statement - Oracle to MariaDB Migration

In Oracle, you can execute a dynamic SQL using the EXECUTE IMMEDIATE statement.

MariaDB supports the EXECUTE IMMEDIATE statement in the Oracle Compatibility mode, but there are some restrictions (see below).

Oracle:

  CREATE OR REPLACE PROCEDURE sp1
  AS
  BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dual';
  END;
  /
  /* Procedure created.*/

MariaDB - Oracle Compatibility:

  SET SESSION sql_mode = 'ORACLE';
 
  DELIMITER //
 
  CREATE OR REPLACE PROCEDURE sp1
  AS
  BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dual';
  END;
  //
  /* Query OK, 0 rows affected */
 
  DELIMITER ;

Oracle allows you to specify the INTO clause to assign the result of the statement:

Oracle:

  CREATE OR REPLACE PROCEDURE sp1
  AS
    cnt NUMBER(10);
  BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dual' INTO cnt;
  END;
  /
  /* Procedure created.*/

MariaDB does not support the INTO clause in the EXECUTE IMMEDIATE statement itself, but you can add it inside the dynamic SQL as follows:

MariaDB:

  SET SESSION sql_mode = 'ORACLE';
 
  DELIMITER //
 
  CREATE OR REPLACE PROCEDURE sp1
  AS
    cnt NUMBER(10);
  BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dual INTO @cnt';
    cnt := @cnt;
  END;
  //
  /* Query OK, 0 rows affected */
 
  DELIMITER ;

In MariaDB, INTO must assign a value to a session variable, otherwise you will get the error: “ERROR 1327 (42000): Undeclared variable: cnt”.

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.