Using LOCAL TEMPORARY TABLES in Oracle

This article is written about how to use the Oracle LOCAL TEMPORARY TABLES with syntax and examples.

Description

Oracle LOCAL TEMPORARY TABLES are wonderful inside modules and embedded SQL packages inside 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 title of the neighborhood brief table that you want to create. column1, column2, … column_n The columns that you desire to create in the local temporary table. Each column need to have a datatype. The column ought to both be described as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Example

Let’s appear 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 instance would create a LOCAL TEMPORARY TABLE referred to as suppliers_temp in Oracle.