LOCAL TEMP TABLES in Oracle

by

As the name itself suggests, these tables are distinct within modules. The Oracle provided the facility to define and scope the Local Temporary Tables to the session in which it is created.

Syntax: To declare a LOCAL TEMPORARY TABLE with a single column. 

DECLARE LOCAL TEMPORARY TABLE table_name  
( column_name data_type [ NULL | NOT NULL ]);

Syntax: To declare a LOCAL TEMPORARY TABLE with multiple columns. 

DECLARE LOCAL TEMPORARY TABLE table_name  
( column_1 data_type [ NULL | NOT NULL ],  
  column_2 data_type [ NULL | NOT NULL ],  
  ...  
  column_n datatype [ NULL | NOT NULL ]  
);

Parameters:
table_name: It is used to specify the name of the local temporary table to be declared.
column definition: It is used to specify the name of the column to be declared in the local temporary table. The next definition is to specify the datatype of the column. The column constraint must also be defined as either NULL or NOT NULL. The column constraint holds a default value of NULL.

Example: Declaring a LOCAL TEMPORARY TABLE with multiple columns. 

DECLARE LOCAL TEMPORARY TABLE retailers  
( retailer_id numeric(10) NOT NULL,  
  retailer_name varchar2(30) NOT NULL,  
  retailer_city varchar2(50)  NULL,  
  retailer_state varchar2(50)  
);

Explanation:
A local temporary table called RETAILERS will be declared. The next would be the declaration of the four columns of the table, namely, the retailer_id, retailer_name, retailer_city and the retailer_state. The first column is of a NUMERIC data type with a maximum limit of 10 digits and it doesn’t accept NULL values. The second column is VARCHAR data type with a maximum limit of 30 characters and it also doesn’t accept NULL values. The third column is also of VARCHAR datatype with a maximum limit of 50 characters and it does accept NULL values. Similarly, the fourth column is also of VARCHAR datatype with a maximum limit of 50 characters and it also does accept NULL values.