Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle Create and Drop Local Temporary Table

Oracle support to create a local temporary table with the help of 'LOCAL TEMPORARY' keyword. Oracle LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within Oracle sessions.

Oracle CREATE LOCAL TEMPORARY TABLE Syntax

To create a local temporary table, use the following syntax:

Syntax

DECLARE LOCAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  column_n datatype [ NULL | NOT NULL ]
);

Parameters:

tablename: The temporary table name that you want to create.

column1, column2: The columns that you wish to create in the local temporary table.

Oracle CREATE LOCAL TEMPORARY TABLE example

The following Oracle statement to create local temporary table:

Example

DECLARE LOCAL TEMPORARY TABLE tblTempCustomer
( custfName varchar2(50) NOT NULL,
  custlName varchar2(50) NOT NULL,
  custAddress varchar2(50)
);

Note:

In the above example, we created local temporary table "tblTempCustomer".

Oracle DROP LOCAL TEMPORARY TABLE Syntax

To remove a temporary table, use the following syntax:

Syntax

DROP TABLE temptablename PURGE;

Parameters:

temptablename: The temporary table name that you want to remove.

Oracle DROP LOCAL TEMPORARY TABLE example

The following Oracle statement to remove existing local temporary table:

Example

DROP TABLE tblTempCustomer PURGE;

Note:

In the above example, we removed a local temporary table called "tblTempCustomer".