Oracle Temporary Table

Definition of Oracle Temporary Table

Oracle temporary table which was introduced in Oracle 8i can be defined as a permanent object where the data is stored in a disk and that particular data is immediately deleted after that particular session or transaction has come to an end and for Oracle private temporary tables are stored in the Oracle database memory and each of these tables are visible only to the session which created that particular table and these tables are generally SQL programs embedded in Oracle sessions.

Syntax:

Temporary tables are of two types: Global Temporary Table and Private Temporary Table introduced in Oracle 18c. When we create any temporary table in the Oracle database it is automatically global. So we need to add Global Keyword.

The syntax of the Global Oracle Temporary table is shown below:

CREATE GLOBAL TEMPORARY TABLE table_name
(column1 datatype [ constraint ],
column2 datatype [ constraint ],
...);

Parameters:

• table_name: It refers to the name of the temporary table that the user is creating.
• Column1, column2: It refers to the name of the columns
• Constraint: It refers to the column constraint which we may or may not add.

How Temporary Table Works in Oracle?

Temporary Table in Oracle as defined earlier is used to store data for some specific task as the temporary table data is deleted as soon as the transaction or session ends or finishes. Suppose we want to extract some data which is not stored in a specific table and is not present in the database. In that case, we can use a procedure to first extract the data from different tables and insert that data into a temporary table during the start of the session or transaction. The data from a temporary table that is created during the starting of the session or transaction can be used throughout the session or throughout the transaction depending upon the permission provided while creating the table. If we give ON COMMIT DELETE ROWS then the table is transaction-specific but if we give ON COMMIT PRESERVE ROWS then the table is session-specific.

How to Create Oracle Temporary Table?

In the previous section of this article, we have learned about how the temporary table works in Oracle. In this section, we will see how we could create a temporary table. The temporary table is just like another table but we have to use the keyword ‘GLOBAL TEMPORARY’ in the create table query. For this example, we will create a temporary table named temp_table having columns named id and description. The id column will be the Primary Key. Let us now look at the query for creating a temporary table.

CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER PRIMARY KEY,
description VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

We can see in the above query that the create table statement ends with ON COMMIT DELETE ROWS which means that the table will be deleted after every transaction. So it is transaction-based. If we want the table created to be session-based which means the data will be present in the temporary table for the whole session and not only the current transaction. Then we need to modify the SELECT query by adding ON COMMIT PRESERVE ROWS clause at the end of the statement.

CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER PRIMARY KEY,
description VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

Let us now run the query in SQL developer.

As we can see in the above screenshot that the temporary table has been created.

How to Insert Data Into Oracle Temporary Table?

Since in the earlier section we can see that the temporary table temp_table has been already created, let us now insert data into the temporary table. In this example, we will insert data into the two columns present in the temp_table. We will not commit the insert statement as we know that the table data contents are transaction specific, so as soon as we sent to commit the data will get deleted. Let us look at the query for the same.

INSERT INTO temp_table VALUES (1, 'First entry');

Let us now run the query in the SQL developer.

As we can see in the above screenshot the row has been inserted into the table.

Let us now use a SELECT query to check the contents of the table. One point to keep in my mind that since this table is transaction specific we will lose the data as soon as we commit the insert statement. So, let us check the contents of the data before we commit the statement.

BEFORE COMMIT

SELECT * FROM temp_table;

Let us run the query in SQL developer.

In the above screenshot, we can see the contents of the table. Let us now commit the transaction and check the rows present in the table.

AFTER COMMIT

SELECT COUNT(*) FROM temp_table;

Let us see the output in the SQL developer.

As per the screenshot, the data got successfully deleted as the count is Zero.

How to Delete?

If the table is created with ON COMMIT DELETE ROWS claws then if we simply commit the data will get deleted as already shown in the previous section. In case the table is created with ON DELETE PRESERVE ROWS then we will use the DELETE statement to delete the rows of the temporary table. Let us look at the query for the same.

DELETE from temp_table2 WHERE id =1;

Let us run the query in SQL developer.

As we can see in the screenshot the row has been deleted successfully.

SELECT COUNT(*) FROM TEMP_TABLE2;

As we can in the screenshot the count present is zero. Hence the delete statement successfully deleted the contents of the table.

Recommended Articles

This is a guide to Oracle Temporary Table. Here we also discuss the Introduction and how the temporary table works in oracle? along with how to delete and insert data into an oracle temporary table. You may also have a look at the following articles to learn more –

1. Oracle Self Join
2. Oracle While Loop
3. Oracle Window Functions
4. Oracle MD5