Oracle / PLSQL: LOCAL TEMPORARY TABLES

This Oracle tutorial explains how to use the Oracle LOCAL TEMPORARY TABLES with syntax and examples.

Description

Oracle LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within Oracle sessions.

Syntax

The syntax for Oracle DECLARE LOCAL TEMPORARY TABLE is:

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

Parameters or Arguments

table_name

The name of the local temporary table that you wish to create.

column1, column2, ... column_n

The columns that you wish to create in the local temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Example

Let's look at an Oracle DECLARE LOCAL TEMPORARY TABLE example:

DECLARE LOCAL TEMPORARY TABLE suppliers_temp
( supplier_id number(10) NOT NULL,
  supplier_name varchar2(50) NOT NULL,
  contact_name varchar2(50)
);

This example would create a LOCAL TEMPORARY TABLE called suppliers_temp in Oracle.