As the name suggests the table is temporary and it will get
deleted after the usage. SQL developer uses temp table and store the some
computation result in it temporarily and query them later. Say for example, if
your computation involves data from 6 or 7 tables, apart from other techniques Temp
table also one nice techniques to make calculations and store it table
temporarily for later use in that session.
To create the temp table, use the following syntax Example:
Select <Column_list> into
#<Temp_Table_name> From <OriginalTableName>
In the above syntax:
Column_List: The list of column from the
main table. You can use comma separated column names or * for all columns
#<Temp_Table_name>:
Name of the temporary table. Ex: #MyTemp
<OriginalTableName>: Name of the
original table from which the data is retrieved
Below is the example screen shot for the usage of the temp
table. The NorthWnd database is used in two different query window.
Query Session7
![Pic01.JPG]()
Query Session5
![Pic02.JPG]()
We have two queries window, and in one query window we are
filtering the employees from Seattle and in other window we are filtering the
employees from London. The information retrieved is stored in the #EmpTemp
table. The table with the # sign is known as temporary table. In
our example it is created for the session for query window shown in red box as
well as green box.
The temp table will get deleted
automatically when the Query window is closed. That means the scope of
the temporary table is connection-based sessions. And note that each query
window maintains separate connection to the database. This is why you get an
error when you try to execute the query shown above again in the same query window.
After retrieving the data, SQL tries to create the temporary table and the
creation fails, as it already exists because of the previous execution in the
same connection to the query window.
Now If you look, I used same #EmpTemp
in the second query window. Does it effects the content of the #EmpTemp
create in the query window marked n red? No. Because, the temp table scope is
session based and SQL see both the temporary table as two different instances
and the same name does not matter.