Creating Temporary Tables
Most of the time, when you create a table in Oracle, the records that eventually populate that table will live inside your database forever (or at least until someone removes them). However, there might be situations where you want records in a table to live inside the database only for a short while. In this case, you can create temporary tables in Oracle, where the data placed into the tables persists for only the duration of the user session, or for the length of your current transaction.
A temporary table is created using the create global temporary table command. Why does a temporary table have to be global? So that the temporary table's definition can be made available to every user on the system. However, the contents of a temporary table are visible only to the user session that added information to the temporary table, even though everyone can see the definition. Temporary tables are a relatively new feature in Oracle, and Oracle hasn't had enough time yet to implement "local" temporary tables (that is, temporary tables that are only available to the user who owns them). Look for this functionality in later database releases. The appropriate create global temporary table command is shown in the following code block:
CREATE GLOBAL TEMPORARY TABLE SCHEMA_NAME.TABLE_NAME
(
COL1 DATATYPE,
COL2 DATATYPE,
.
.
.
COLNn DATATYPE
)
ON COMMIT DELETE ROWS
NOCACHE;
(
COL1 DATATYPE,
COL2 DATATYPE,
.
.
.
COLNn DATATYPE
)
ON COMMIT DELETE ROWS
NOCACHE;
There are two options available for ON COMMIT clause
DELETE ROWS: truncate data after each commit.
PRESERVE ROWS: truncate table when session ends.
Example of usage of GLOBAL TEMPORARY TABLE:
There can be several scenarios where using a PL/SQL table is not suitable or may be more complicated than using a temporary table.
For example suppose you have some data coming into a remote database at regular intervals. You want to transfer those data into your local database but only after processing. Once you process your data you may want to join these data with other tables in your local database and then based on the results you will insert them into your local database.
In this case you can create a global temporary table and perform the steps as follows:
1. Get your remote data into a cursor using database link
2. Processes your data based on your requirement (such as filtering/modifying).
3. Insert the changed data into a global temporary table.
4. Join the global temporary table with other local oracle tables based on your requirement.
5. Put the resultant data into its final destination table in your local database or to another remote database.
Limitations:
However there are some limitations in using global temporary table. They can not have any foreign keys; they can not have any storage clause and they can not be partitioned.
Create global temporary table XXX_PO_HEADERS_ALL as
Select *
From PO_HEADERS_ALL
Where 10=11
The purpose of writing the where clause is to make the temporary table blank. If we dont put the where clause the temporary table would contain all the rows of XXX_PO_HEADERS_ALL
create global temporary table
XXX_PO_HEADERS_ALL (...)
on commit preserve rows;
The “on commit preserve rows” clause tells the SQL engine that when a transaction is committed the table should not be cleared.
