How do you create a temporary table in an Oracle database

Question

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

User · Answer

CREATE TABLE table temp list objects AS SELECT o owner  o object name FROM sys all objects o WHERE o object type   TABLE

User · Answer

CREATE GLOBAL TEMPORARY TABLE Table name      startdate DATE       enddate DATE       class CHAR 20     ON COMMIT DELETE ROWS

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to temp-tables