How to create a temporary table in Oracle

Question

Hi

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

something like

Declare table @table (int id)

In SQL server

And than populate it with a select statement

Is it possible?

Thanks

Answer 1

Yep, oracle has temporary tables. Here is a link to an AskTom article describing them.
And here is the official oracle CREATE TABLE documentation (scroll down to the CREATE GLOBAL TEMPORARY section)
And an example:

CREATE GLOBAL TEMPORARY TABLE today_sales
   ON COMMIT PRESERVE ROWS 
   AS SELECT * FROM orders WHERE order_date = SYSDATE;

Answer 2

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 when you end you session ( or end of the transaction, depending on which "ON COMMIT" clause you use).