-2

Possible Duplicate:
How to create a temporary table in Oracle

I have a SQL query that produces a table that I would like to reuse several times. In T-SQL I would create a temporary table (#temp), what is the equivalent in Oracle SQL?

Thanks.

Community
  • 1
  • 1
Josh
  • 307
  • 5
  • 10
  • What does google have to say? – wadesworld Jun 21 '12 at 03:18
  • Google talks about global temporary tables, but in SQL no specific permission is needed to create #temp tables. When I try to create a global temp table in oracle it says I don't have permission. – Josh Jun 21 '12 at 03:31
  • Oracle doesn't have a way to create temp tables without specific permissions. Ask your DBA to grant the CREATE TABLE permission to you. If you need temporary storage in PL/SQL, consider using a [PL/SQL collection](http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/05_colls.htm) type. – N West Jun 21 '12 at 03:35
  • 1
    @N. West, that's bad advice and not the way temporary tables work in oracle – steve Jun 21 '12 at 05:15
  • In general you don't need temp tables as much in Oracle as in SQL Server (because of it's more efficient locking implementation). In most of the cases I have seen you can simply replace the SQL Server temp table with the corresponding select (or use a WITH clause) –  Jun 21 '12 at 06:10

1 Answers1

1

Temporary tables work differently in oracle than in sql server. I would recommend to read the corresponding chapter in the oracle documentation. To reuse the the contents of the table use the "preserve rows" clause on commit.

steve
  • 5,870
  • 1
  • 21
  • 22