oracle temporary table with code examples

Oracle Temporary Table with Code Examples

Temporary tables are used in Oracle to store and manage temporary data. These tables are created in the database and they are temporary in nature, which means they are only available for a particular session or transaction. The data in the temporary tables is stored in the memory, and it is automatically deleted when the session or transaction ends. Oracle provides different types of temporary tables, and one of them is the Global Temporary Table. In this article, we will discuss Oracle Temporary Tables with code examples, including Global Temporary Tables.

Oracle Temporary Table – Overview

An Oracle Temporary Table is a type of table that is created temporarily to store data, and its data is deleted automatically at the end of a transaction or session. The temporary table is created in the TEMP tablespace, which is pre-created in the Oracle database, and is used to store temporary data objects.

Temporary tables can be used to store data that is required for a short period of time, and they can be used in different types of scenarios such as:

1. Storing intermediate or temporary results during a long-running transaction or batch operation.
2. Storing data when it is being processed in stages, where different stages require different types of data.
3. Storing data in a session-specific manner, such as when you need to maintain state across multiple calls to a program or stored procedure.

Oracle provides two types of temporary tables:

1. Global Temporary Table: It is a permanent table that is created once and reused multiple times by different sessions. The data is stored in the temporary table for the duration of a session, and it is automatically deleted when the session ends.
2. Private Temporary Table: It is a temporary table that is created for a specific session. The data is stored in the temporary table for the duration of the session, and it is automatically deleted when the session ends.

Creating an Oracle Global Temporary Table

We can create a global temporary table in Oracle using the CREATE GLOBAL TEMPORARY TABLE statement. The general syntax for creating a Global Temporary Table is as follows:

CREATE GLOBAL TEMPORARY TABLE temp_table
(
column1 datatype1 [ NULL | NOT NULL ],
column2 datatype2 [ NULL | NOT NULL ],
….
)
[ON COMMIT DELETE ROWS];

The above syntax will create a Global Temporary Table called temp_table and defines its columns. We can specify the ON COMMIT DELETE ROWS option to delete all the rows in the temporary table when a transaction or session ends.

Example:

Let’s consider a simple example of a Global Temporary Table that stores the details of students. We will create a temporary table called temp_student, which has columns such as ID, NAME, and AGE.

CREATE GLOBAL TEMPORARY TABLE temp_student
(
id NUMBER(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
age NUMBER(3)
)
ON COMMIT DELETE ROWS;

Here, we have created a Global Temporary Table called temp_student that has three columns: id, name, and age. The id and name columns are defined as NOT NULL, which means these columns cannot contain null values.

Inserting Data into the Global Temporary Table

Once we have created the temporary table, we can insert data into it using the INSERT statement. The general syntax for inserting data into a Global Temporary Table is as follows:

INSERT INTO temp_table (column1, column2, ….)
VALUES (value1, value2, …..);

For example, let’s insert some data into the temp_student table:

INSERT INTO temp_student (id, name, age)
VALUES (1, 'John', 23);

INSERT INTO temp_student (id, name, age)
VALUES (2, 'Jane', 25);

INSERT INTO temp_student (id, name)
VALUES (3, 'Alex');

Here, we have inserted three rows into the temp_student table. Note that we have not specified any value for the age column in the third row, as it is defined as NULL.

Retrieving Data from the Global Temporary Table

To retrieve data from the Global Temporary Table, we can use the SELECT statement. The general syntax for selecting data from a Global Temporary Table is as follows:

SELECT column1, column2, ….
FROM temp_table

For example, let’s retrieve the data from the temp_student table:

SELECT * FROM temp_student;

Here, we have used the SELECT statement to retrieve all the rows from the temp_student table.

Deleting Data from the Global Temporary Table

To delete data from the Global Temporary Table, we can use the DELETE statement. The general syntax for deleting data from a Global Temporary Table is as follows:

DELETE FROM temp_table;

For example, let’s delete all the rows from the temp_student table:

DELETE FROM temp_student;

We have used the DELETE statement to delete all the rows from the temp_student table. Note that this will delete the rows from the temporary table only for the current session.

Conclusion

In this article, we have discussed the Oracle Temporary Table with code examples. We have learned about the Global Temporary Table, which is a type of temporary table in Oracle that is used to store data temporarily for a particular session or transaction. We have also learned how to create, insert data, select data, and delete data from the Global Temporary Table using the SQL statements. Temporary tables are an important feature of the Oracle database that can help us manage temporary data more efficiently.

Global Temporary Tables are a unique feature of Oracle databases as they offer a solution to manage data that is temporary and needs to be accessible by multiple sessions or transactions. The Global Temporary Tables act as a permanent structure but are temporary by nature. They are created once and can be reused by different sessions and transactions.

When the Global Temporary Table is created under a specific name, Oracle automatically creates a corresponding structure in the database’s TEMP tablespace. Therefore, it is temporary in nature and disappears when the database is shut down or when the session ends. This feature makes Global Temporary Tables resource-friendly, as it does not occupy permanent space. It can also be used to store intermediate or temporary results during a long-running transaction or batch processing.

Oracle allows users to specify the type of data that needs to be stored or retrieved from a Global Temporary Table by defining the columns and the data type of the columns in the Global Temporary Table.

Creating a Global Temporary Table in Oracle SQL is a simple process, using the CREATE GLOBAL TEMPORARY TABLE statement. The code can be customized as per the requirements of the user.

In an Oracle Global Temporary Table, data can be inserted into the table in the following formats:

1. BATCH INSERTIONS: When data for each row is declared once and all values are passed in a single large array.
2. SINGLE ROW INSERTIONS: When the data for each row is declared individually.

Regardless of which method is used to insert data into the Global Temporary Table, Oracle ensures that the data is stored locally in the Global Temporary Table and is specific to the user’s session.

Retrieving data from the Oracle Global Temporary Table can be done in a variety of ways using statements such as SELECT, INSERT, and DELETE from SQL. Global Temporary Tables can be further extended to reference them from another application by creating a synonym.

The main advantage of Oracle Global Temporary Tables is that they only exist for the duration of the session so that the data remains confidential and secure. Additionally, the database is only accessed at the application level for the duration of the session, which reduces the load on the database and reduces the risk of data corruption.

In conclusion, Oracle Global Temporary Tables are a valuable tool in managing temporary data in the Oracle database. They provide a flexible and safe solution for storing temporary data for efficient and streamlined processing of applications. With its numerous features, it is an essential tool for modern enterprises, including financial institutions and healthcare organizations, which handle large quantities of sensitive data.

Popular questions

1. What is an Oracle Temporary Table?

Answer: An Oracle Temporary Table is a type of table that is created temporarily to store data, and its data is deleted automatically at the end of a transaction or session.

2. What is the difference between a Global Temporary Table and a Private Temporary Table?

Answer: A Global Temporary Table is a permanent table that is created once and can be reused by different sessions, whereas a Private Temporary Table is a temporary table that is created for a specific session.

3. How can you create an Oracle Global Temporary Table?

Answer: You can create an Oracle Global Temporary Table using the CREATE GLOBAL TEMPORARY TABLE statement. For example:

CREATE GLOBAL TEMPORARY TABLE temp_table
(
column1 datatype1 [ NULL | NOT NULL ],
column2 datatype2 [ NULL | NOT NULL ],
….
)
[ON COMMIT DELETE ROWS];

4. How can you insert data into the Global Temporary Table?

Answer: You can insert data into the Global Temporary Table using the INSERT statement. The syntax for doing so is as follows:

INSERT INTO temp_table (column1, column2, ….)
VALUES (value1, value2, …..);

5. How can you delete data from the Global Temporary Table?

Answer: You can delete data from the Global Temporary Table using the DELETE statement. The syntax for doing so is as follows:

DELETE FROM temp_table;

Tag

TemporalOracle