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:
|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|
You can use INTO TEMP clause of the SELECT statement to create a temporary table and insert rows:
-- 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.
-- 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:
-- 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:
-- 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:
-- Remove all rows of the current session TRUNCATE TABLE cities_temp;