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, you 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:

   -- 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:

  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:

  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.