CREATE GLOBAL TEMPORARY TABLE - Oracle to MariaDB Migration

In Oracle, you can create a global temporary table (GTT) whose definition is visible to all sessions but data is only visible within the session that inserted it.

In MariaDB Oracle Compatibility mode, you still have to create a temporary table in each session, as temporary table definitions are not global, they are session-specific and not shared across sessions.

Oracle

   -- A sample temporary table
   CREATE GLOBAL TEMPORARY TABLE gtt_colors 
   (
     name VARCHAR(30)
   ) ON COMMIT DELETE ROWS;

MariaDB - Oracle Compatibility:

   -- A sample temporary table
   CREATE TEMPORARY TABLE gtt_colors 
   (
     name VARCHAR(30)
   );

If ON COMMIT DELETE ROWS is defined (this the default), then all rows are deleted after each commit:

Oracle:

  INSERT INTO gtt_colors VALUES ('White');
  /* 1 row created. */
 
  SELECT * FROM gtt_colors; 
  /* White */
 
  COMMIT;
 
  SELECT * FROM gtt_colors;
  / * no rows selected */

MariaDB - Oracle Compatibility:

  START TRANSACTION;
 
  INSERT INTO gtt_colors VALUES ('White');
  /* Query OK, 1 row affected */
 
  SELECT * FROM gtt_colors; 
  /* White */
 
  COMMIT;
 
  -- Row was not deleted on commit
  SELECT * FROM gtt_colors;
   /* White */
 
   -- Delete all rows explicitly
   DELETE FROM gtt_colors;
   /* Query OK, 1 row affected */
 
   SELECT * FROM gtt_colors;
   /* Empty set */

Note that if you use TRUNCATE TABLE in MariaDB to delete rows from a temporary table, it causes an implicit commit. :

MariaDB - Oracle Compatibility:

  INSERT INTO gtt_colors VALUES ('White');
  /* Query OK, 1 row affected */
 
  SELECT * FROM gtt_colors; 
  /* White */
 
  START TRANSACTION;
 
  -- Delete all rows 
  DELETE FROM gtt_colors;
   /* Query OK, 1 row affected */
 
  -- We can return the deleted rows
  ROLLBACK;
 
  SELECT * FROM gtt_colors;
   /* White */  
 
  COMMIT;
 
  START TRANSACTION;
 
  TRUNCATE TABLE gtt_colors;
 
  -- Rows are lost as TRUNCATE committed its change 
  ROLLBACK;
 
  SELECT * FROM gtt_colors;
  /* Empty set */

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