Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you create a temporary table in an Oracle database?

I would like to create a temporary table in a Oracle database

something like

Declare table @table (int id) 

In SQL server

And then populate it with a select statement

Is it possible?

Thanks

like image 215
GigaPr Avatar asked Apr 19 '10 23:04

GigaPr


People also ask

What is temporary table in Oracle with example?

In SQL Server developers will regularly create a temporary table to do some work and drop it. In Oracle a Global Temporary Table (GTT) is a permanent metadata object that holds rows in temporary segments on a transaction-specfic or session-specific basis. It is not considered normal to create and drop GTTs on the fly.

How do you create a temporary table?

To create a Global Temporary Table, add the “##” symbol before the table name. Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have an Unique Table Name.

Which command would create a temporary table?

Syntax. The CREATE TEMPORARY TABLE statement is used to create a temporary table. CREATE TEMPORARY TABLE table_name (column definitions); If you want to create a temporary table from scratch, you can use the TEMPORARY keyword when creating a table, i.e. use CREATE TEMPORARY TABLE instead of CREATE TABLE statement.


2 Answers

Yep, Oracle has temporary tables. Here is a link to an AskTom article describing them and here is the official oracle CREATE TABLE documentation.

However, in Oracle, only the data in a temporary table is temporary. The table is a regular object visible to other sessions. It is a bad practice to frequently create and drop temporary tables in Oracle.

CREATE GLOBAL TEMPORARY TABLE today_sales(order_id NUMBER) ON COMMIT PRESERVE ROWS; 

Oracle 18c added private temporary tables, which are single-session in-memory objects. See the documentation for more details. Private temporary tables can be dynamically created and dropped.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_today_sales AS SELECT * FROM orders WHERE order_date = SYSDATE; 

Temporary tables can be useful but they are commonly abused in Oracle. They can often be avoided by combining multiple steps into a single SQL statement using inline views.

like image 194
hamishmcn Avatar answered Oct 26 '22 18:10

hamishmcn


Just a tip.. Temporary tables in Oracle are different to SQL Server. You create it ONCE and only ONCE, not every session. The rows you insert into it are visible only to your session, and are automatically deleted (i.e., TRUNCATE, not DROP) when you end you session ( or end of the transaction, depending on which "ON COMMIT" clause you use).

like image 43
Matthew Watson Avatar answered Oct 26 '22 19:10

Matthew Watson