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".