Temporary Tables - Informix to Oracle Migration

In Informix, a session can create a temporary table to hold the query results. Both the definition and data of this temporary table are not visible to other users or sessions.

The temporary table exists until the end of session, or until DROP TABLE statement is issued on the temporary table.

In Oracle, typically there is a permanent database object created using CREATE GLOBAL TEMPORARY TABLE statement, and its definition is visible to each session. But each session can only access its own data.

Temporary tables in Informix and Oracle:

You can use INTO TEMP clause of the SELECT statement to create a temporary table and insert rows:

Informix:

  -- Create temporary table "cities_temp" and insert rows from "cities" table
  SELECT name, state FROM cities
  INTO TEMP cities_temp;

Although each session can execute CREATE GLOBAL TEMPORARY statement in Oracle, the table definition becomes visible to other sessions, and in multi-session environment you cannot create temporary tables with different definitions but the same names in Oracle.

Typical approach in Oracle is to create a temporary table as part of the database schema definition, similar as you create regular tables, views and so on. Then each session can access temporary tables, but Oracle ensures that each session can have access to its own data only.

Oracle:

  -- Create temporary tables as part of DDL creation, not session runtime
  CREATE GLOBAL TEMPORARY TABLE cities_temp
  (
      name VARCHAR2(70),
      state CHAR(2)
   ) ON COMMIT PRESERVE ROWS;

Then each session can start inserting data into the existing table, no need to create the temporary table each time:

Oracle:

  -- Insert rows into existing temporary table
  INSERT INTO cities_temp
  SELECT name, state FROM cities;

When you do not need a temporary table anymore, you can execute DROP TABLE statement to remove the table in Informix:

Informix:

  -- Drop temporary table 
  DROP TABLE  cities_temp;

In Oracle you can just remove rows inserted by the current session, you do not need to drop the temporary table:

Oracle:

  -- Remove all rows of the current session 
  TRUNCATE TABLE  cities_temp;

• Informix to Oracle Migration Tools and Reference
• Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - August 2013.