123

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

Vadzim
  • 24,954
  • 11
  • 143
  • 151
GigaPr
  • 5,206
  • 15
  • 60
  • 77

5 Answers5

167

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
hamishmcn
  • 7,843
  • 10
  • 41
  • 46
  • 48
    No downvote, but I would never recommend a CREATE TABLE AS SELECT for a global temporary table - a less experienced developer might get the wrong idea that they have to populate GTTs this way every time. Sure, this is a convenient way of copying the definition from another table, but the example will probably confuse some people. – Jeffrey Kemp Apr 20 '10 at 01:15
  • I totally agree with Jeffrey Kemp here, this is just one way of creating this table, I ran into this, and realized man, do I have to put this select statement in my DDL. – Vijay Kumar Aug 07 '14 at 14:50
  • 4
    you can just add "where 1=0"; – Palcente Aug 01 '17 at 13:46
  • @Palcente that still implicates you'd need a "real table" with the same structure in order to create a temporary one, which is not the case (also see [this answer](/a/14643253/2533433). Apart from that, this answer misses to point out there's not just the ***GLOBAL*** temporary table, but one can also use "ordinary" temporary tables. The difference to SQL Server, as [pointed out by Matthew](/a/2671612/2533433), is another important point one should make sure not to miss (or wonder about errors on "the second call" about the object already existing ;) – Izzy Aug 23 '17 at 08:50
  • 2
    Valid use case for `CREATE TABLE AS SELECT`: select from remote tables with LOB columns, since you can't directly `SELECT`from them. I'm writing a procedure to copy records from a remote database, and this was my solution: bring the records into a GTT, and then copy from that to the "real" table. – rgoliveira Jan 09 '19 at 18:53
  • Does Oracle support the `create table new like old` syntax of other SQL implementations? – Tom Jun 12 '20 at 14:29
84

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).

Sarath Chandra
  • 1,850
  • 19
  • 40
Matthew Watson
  • 14,083
  • 9
  • 62
  • 82
  • 18
    I don't fully understand. You said you create it once and only once, and not every session. However you also said that the temporary table is deleted every session. Doesn't that mean you'll need to create the temporary table every session? – Kias Mar 26 '14 at 07:38
  • 32
    `DELETED`, not `DROP`ped. – Tripp Kinetics Jun 30 '14 at 20:34
  • 13
    You dont create temporary table every session, but once. The table is available across the system. The table is accessible from any session that you create, including parallel sessions. but when you insert data from inside one session, that data is not visible from other parallel sessions across the system. One session's data is property of that session only. Thus the data is session specific, not visible systemwise. Hence when you close one session, that session's data is deleted from the table. – SubhasisM Sep 08 '16 at 13:30
42
CREATE GLOBAL TEMPORARY TABLE Table_name
    (startdate DATE,
     enddate DATE,
     class CHAR(20))
  ON COMMIT DELETE ROWS;
kleopatra
  • 51,061
  • 28
  • 99
  • 211
harish from nit ameerpet
  • 421
  • 4
  • 2
  • 12
    Note that the rows will always be deleted at the end of the session; `ON COMMIT DELETE ROWS` means they'll be discarded sooner, if you commit incrementally during a session. –  Jan 06 '15 at 16:05
  • 1
    zygimantus, yes, you can delete manually during the session. During the session, the temporary table behaves exactly as a normal table. The only difference between a temporary table and a real table, from a user's perspective, is that all rows are deleted once the session ends and no other session can read the contents of the table used by the session. There is an instance of the table per session; two sessions can use the same temporary table at the same time and have two different sets of rows, without any conflict. – Hans Deragon Nov 01 '17 at 14:23
2

If you are trying to create something similar to SQL Server where you SELECT INTO #TEMP_TABLE_NAME this cannot be achieved in Oracle using CREATE TABLE unless you have security to do so which is probably unlikely if you are a regular report writer.

In Oracle, if you are trying to store the output of a SELECT block in memory to increase query performance, you'll need to use the Oracle hint /+ MATERIALIZE/ in the main SELECT of a CTE. This will store the results of the CTE in memory and can be accessed in other parts of your query without running the risk of re-executing the CTE each time it is referenced.

Example:

WITH CTE AS (
    SELECT /*+ MATERIALIZE*/
    'FOO' AS "STUFF"
    FROM DUAL
)

SELECT * FROM CTE

If you check the execution plan after, you'll see that the object will be read in as "Cursor Duration Memory".

Hakuna Patata
  • 41
  • 4
-5
CREATE TABLE table_temp_list_objects AS
SELECT o.owner, o.object_name FROM sys.all_objects o WHERE o.object_type ='TABLE';
double-beep
  • 5,031
  • 17
  • 33
  • 41
Edy Muniz
  • 1
  • 2