Using the private temporary table emulation

How does the private temp table emulation work?

• Informix CREATE TEMP TABLE and SELECT INTO TEMP statements are automatically converted to Oracle "CREATE PRIVATE TEMPORARY TABLE". The name of the temporary table is converted to a table name with the prefix defined by the private_temp_table_prefix system parameter (default is ORA$PTT_).
• Since Oracle private temporary tables are only visible to the current user and SQL session, it is not possible to specify a schema prefix.
• To mimic the behavior of Informix temporary tables, Oracle private temporary tables are created with the ON COMMIT PRESERVE DEFINITION clause. The temp table will then remain when a transaction ends.
• By default, private temporary tables are created in the default temporary table space. If needed, you can specify another tablespace name for Oracle private temporary tables with the following FGLPROFILE entry:

  dbi.database.dsname.ora.temptables.tablespace = "mytemptabs"

  Note: This tablespace must be a temporary tablespace.
• Once the temporary table has been created, all other SQL statements performed in the current SQL session are parsed to convert the original table name to the corresponding private temporary table name.
• When the BDL program disconnects from the database, Oracle will automatically drop private temporary tables created during the SQL session.

Prerequisites when using the private temp table emulation

• By default (with Oracle 18.3), the maximum number of private temporary tables allowed per user is 16. Consider increasing this number, if your programs create more temporary tables. Otherwise, Oracle will produce the error "ORA-32460: maximum number of private temporary tables per session exceeded". The max number of private temp tables can be changed with the _ptt_max_num hidden system parameter:

  ALTER SYSTEM SET "_ptt_max_num"=32 SCOPE = BOTH;

• For more details, check also "CREATE PRIVATE TEMPORARY TABLE" in the Oracle documentation.

Limitations of the private temp table emulation

• By default (in Oracle 18.3), the max number of private temp tables per user is 16. This can be changed by a system parameter.
• It is not possible to define table constraints such as NOT NULL or PRIMARY KEY, etc.
• Index creation is not allowed on private temp tables. Therefore, reports with internal ORDER BY cannot work.
• Columns can't have DEFAULT values. Thus, the native2 serial emulation cannot work.
• Triggers are not supported. Therefore, native and regtable serial emulations cannot work with private temp tables.
• There is no such concept as Informix CREATE TEMP TABLE ... WITH NO LOG. By default, Oracle private temporary table data is always logged for transaction rollback.
• Tokens matching the original table names are converted to unique names in all SQL statements. Make sure you are not using the temp table name for other database objects, like columns. The following example illustrates this limitation:

  CREATE TABLE tab1 ( key INTEGER, tmp1 CHAR(20) )
  CREATE TEMP TABLE tmp1 ( col1 INTEGER, col2 CHAR(20) )
  SELECT tmp1 FROM tab1 WHERE ... 

Serial emulation with private temporary tables