Database Reference
In-Depth Information
You can check whether a table has been created as temporary and the duration of the data (per session or
transaction) by querying the
TEMPORARY
and
DURATION
columns of
USER_TABLES
view. The default
DURATION
is
SYS$TRANSACTION
(meaning
ON COMMIT DELETE ROWS
). Here's what these values look like for this example:
EODA@ORA12CR1> select table_name, temporary, duration from user_tables;
TABLE_NAME T DURATION
------------------------- - ---------------
TEMP_TABLE_TRANSACTION Y SYS$TRANSACTION
TEMP_TABLE_SESSION Y SYS$SESSION
If you have experience of temporary tables in SQL Server and/or Sybase, the major consideration for you is that
instead of executing
SELECT X, Y, Z INTO #TEMP FROM SOME_TABLE
to dynamically create and populate a temporary
table, you will
•
Create all your global temporary tables once, as part of the application installation, just as you
create permanent tables.
INSERT INTO TEMP (X,Y,Z) SELECT X,Y,Z FROM SOME_TABLE
.
•
In your procedures, simply
Just to drive home the point, the goal here is not to create tables in your stored procedures at runtime. That is
not the proper way to use temporary tables in Oracle. DDL is an expensive operation; you want to avoid doing that
at runtime. The temporary tables for an application should be created during the application installation—
never
at
runtime. The pitfalls you will encounter if you attempt to dynamically create the global temporary tables (or just tables
in general) at runtime in PL/SQL will be:
•
You will be doing DDL at runtime. DDL is extremely expensive, it involves hundreds of
recursive SQL statements. DDL involves a lot of serialization (one at a time, get in line).
•
You will have to use dynamic SQL in your PL/SQL to use these tables. You lose all of the
benefits of static, compile-time SQL. This is a huge loss.
•
You will not be able to run two copies of your stored procedure at the same time, ever. Since
both stored procedure instances would attempt to drop and create the same temporary table,
they would conflict with each other (in this scenario you could dynamically generate a unique
name for the temporary table each time the procedure is created, but this would introduce
complexity and potential headaches when troubleshooting).
•
You will end up having your tables stick around some day—that is, your code will not
drop them correctly. Due to an unforeseen error (a power failure is all it would take), your
procedure might not complete. Your table will still be there when power is restored. You will
have to manually clean up objects from time to time.
In short, there are no good reasons to create tables in PL/SQL at runtime, only reasons to
not
ever create tables in
PL/SQL at run time.
Temporary tables can have many of the attributes of a permanent table. They may have triggers, check
constraints, indexes, and so on. Features of permanent tables that they do not support include the following:
•
They cannot have referential integrity constraints. Neither can they be the
target
of a foreign
key, nor can they have a foreign key defined on them.
NESTED TABLE
type columns. In Oracle9
i
and earlier, they cannot have
VARRAY
type columns either; this restriction was lifted starting with Oracle 10
g
.
•
They cannot have
•
They cannot be IOTs.
