Using the default temporary table emulation

How does the default emulation work?

Informix^® CREATE TEMP TABLE and SELECT INTO TEMP statements are automatically converted to ORACLE "CREATE TABLE". The name of the temporary table is converted to a unique table name.
Tables are created in the current schema.
Temporary tables are created with the option TABLESPACE TEMPTABS so that data is stored in a dedicated tablespace named "TEMPTABS". Of course the TEMPTABS tablespace must exist before running programs, otherwise temporary table creation will fail. You create a tablespace with the CREATE TABLESPACE SQL command. Using a specific tablespace for temporary tables allows you to specify storage options, for example to use a physical device which can be different from the disk drive used for real data storage. Additionally, backups of permanent application tables can be performed without the data of temporary tables.
Starting with Oracle 10g, dropped tables are saved in the recycle bin by default. You may want to avoid the recycle bin feature at the database level or session level with:
```
ALTER SYSTEM SET recyclebin = OFF scope=both
```
or:
```
ALTER SESSION SET recyclebin = OFF
```
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 unique table name.
When the BDL program disconnects from the database (for example, when it ends or when a CLOSE DATABASE instruction is executed), the tables which have not been removed with an explicit "DROP TABLE" are automatically removed by the database interface. However, if the program crashes, the tables will remain in the database, so you may need to cleanup the database from time to time.

Prerequisites when using the default emulation

Application users must have sufficient privileges to create database tables in their own schema (usually, "CONNECT" and "RESOURCE" roles).
When using the default emulation based on permanent tables, you must create a dedicated tablespace named "TEMPTABS".
The TEMPTABS tablespace must be of type "permanent", as it will hold permanent tables used to emulate Informix temp tables.

Make sure it is big enough to hold all the data, and check for automatic extension.

For more details, see "CREATE TABLESPACE" in the Oracle documentation.

Limitations of the default emulation

When using the default emulation, the real name of an emulated temporary table will have the following format:
ttnumber_original_name

Where <number> is the Oracle AUDSID session id returned by:
```
SELECT USERENV('SESSIONID') FROM DUAL
```
As Oracle 9i and 10g table names can't exceed 30 characters in length, and since session ids are persistent over server shutdown, you must pay attention to the names of your temporary tables. For example, if you create a temp table with the name TEMP_CUSTOMER_INVOICES (22c) it leaves 30 - (3 + 22) = 5 characters left for the session id, which gives a limit of 99999 sessions.

To workaround this limitation, you can provide your own SQL command to generate a unique session id with the following FGLPROFILE entry:

dbi.database.dbname.ora.sid.command = "select ..."

As an example, you can use the SID column value from V$SESSION:
```
SELECT SID FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID')
```
You are not allowed to use the unique table name format in your own database schema. Make sure you are not using table or column names with the following format:
ttnumber_original_name
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 ... 
```

Maintenance of default emulation

If you want to list the tables created by a specific user, do this:
```
SELECT * FROM ALL_TABLES WHERE OWNER = 'user_name'
```
As with other database object names, the user name is stored in uppercase letters if it has been created without using double quotes (create user scott ... = stored name is "SCOTT" ).

Creating indexes on temporary tables with default emulation

Indexes created on temporary tables must have unique names also. The database interface detects CREATE INDEX statements which are using temporary tables and converts the index name to unique names.
DROP INDEX statements are also detected to replace the original index name by the real name.

SERIALs in temporary table creation with default emulation

You can use the SERIAL data type when creating a temporary table.
Sequences and triggers will be created in the current schema.

See issue about SERIALs for more details.