Local Temporary Tables - (Oracle Tables)

Local temporary tables in Oracle are tables that are created on the fly and exist only for the duration of a session. They are useful for storing temporary data that needs to be used during the execution of a single query or stored procedure. Once the session ends, the table is automatically dropped.

Syntax

The syntax for creating a local temporary table in Oracle is as follows:

CREATE GLOBAL TEMPORARY TABLE table_name
(
   column1 datatype [ NULL | NOT NULL ],
   column2 datatype [ NULL | NOT NULL ],
   ...
) ON COMMIT [ PRESERVE ROWS | DELETE ROWS ];

Here, table_name is the name of the temporary table, and column1, column2, etc. are the column names and data types of the table. The ON COMMIT clause determines what happens to the table data at the end of the transaction. PRESERVE ROWS retains the data, and DELETE ROWS deletes it.

Example

Here is an example of creating and using a local temporary table in Oracle:

CREATE GLOBAL TEMPORARY TABLE emp_temp (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    emp_dept VARCHAR2(50)
) ON COMMIT PRESERVE ROWS;

INSERT INTO emp_temp (emp_id, emp_name, emp_dept)
SELECT employee_id, first_name || ' ' || last_name, department_name
FROM employees e
JOIN departments d 
ON e.department_id = d.department_id
WHERE department_name = 'Sales';

SELECT *
FROM emp_temp;

Output

The output of the above example would be the data from the emp_temp table, which contains the employee ID, name, and department for all employees in the Sales department.

Explanation

In the example above, we first create a global temporary table called emp_temp with three columns: emp_id, emp_name, and emp_dept. We then insert data into the table by selecting the employee ID, first and last name (concatenated), and department name from the employees and departments tables, respectively, for all employees in the Sales department.

Finally, we select all data from emp_temp to verify that the data was inserted correctly.

Use

Local temporary tables in Oracle are useful for storing and manipulating temporary data during the execution of a single session or transaction. They can be used in stored procedures, triggers, and other database objects to simplify complex queries and improve performance.

Important Points

• Local temporary tables are temporary tables that exist only for the duration of a session.
• They are useful for storing and manipulating temporary data during the execution of a single query or stored procedure.
• The ON COMMIT clause determines what happens to the table data at the end of the transaction.
• Temporary tables can be used in stored procedures, triggers, and other database objects to simplify complex queries and improve performance.

Summary

In summary, local temporary tables in Oracle are a powerful feature that can help simplify complex queries and improve performance. They are created on the fly and exist only for the duration of a session, making them ideal for storing and manipulating temporary data during the execution of a single query or stored procedure. The ON COMMIT clause determines what happens to the data in the table at the end of the transaction.