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:

Temporary Table Informix Oracle
Creation Dynamically created by a session Permanent database object
Definition visible to other sessions No Yes
Data visible to other sessions No No
Definition Duration Until end of session or DROP TABLE Permanent
Data Duration Until end of session or transaction

Create a Temporary Table in Informix

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;

Create a Temporary Table in Oracle

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;

Drop a Temporary Table in Informix

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;

Truncate a Temporary Table in Oracle

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;

Database and SQL Migration Tools

About SQLines

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.

You could leave a comment if you were logged in.