0

Does there exist any In-Memory object like the Microsoft SQL Server Temporary tables that can be used for Oracle too?

UPDATE:

I asked about Temp tables because I have to copy X dataset and Insert them again in the same table And do some updates on the fields of the copied dataset. The copy action alone would cause a primary constraint exception, therefore I have to do a Insert Into(Select... into an In-Memory object, do updates there and then write back all changes dataset into the original table. What is better suited Temp Table or Table variable? Consider I use ADO.NET. So ONE statement which is executed must work for Oracle AND MS Sql Server without exception due to language differences.

Pascal
  • 12,265
  • 25
  • 103
  • 195
  • Temporary tables in SQL Server are *not* in-Memory. They exist in the `tempdb` database. Even table variables, under memory pressure, may be pushed to the `tempdb` database. In fact, the schema of a table variable is always created in `tempdb`. – Joe Stefanelli Feb 15 '12 at 15:30
  • @Joe As long I can do temporary stuff in a table that is not my original table the tempdb database would be fine. – Pascal Feb 15 '12 at 16:17

2 Answers2

3

Yes. Oracle has temporary tables too. Or perhaps you were asking about Table variables (hard to tell from your question)? They answer to that is also yes.

FYI: Temporary tables in SQL aren't guaranteed to be "in memory"

Update: To answer the new question you added to the OP. Use whatever works best for your code. I don't think it makes a substantial different whether you use table varaibles or "real" temporary tables. A better question is whether to use global or local temporary tables. In that case, I'd say avoid the globals unless you don't have a choice.

Community
  • 1
  • 1
JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • To make it clear. Is the syntax for creating/using the temp table the same in Oracle + Sql Server? Andy Skirrow (post is at bottom) said I have to setup the temp table when the database is installed? I do not want that. – Pascal Feb 15 '12 at 18:52
  • No the syntax is not the same. Oracle `create global temporary table table_name ...` Sql Server `create table #table_name ....` Oracle temporary tables are permanent objects that hold temporary data that has session visibility. (The global temp table is global, but the data it holds is not) Where as SQL Server temp tables are temporary objects where the object scope and data is either both local or both global. – Shannon Severance Feb 16 '12 at 09:41
  • Actually your answer is a solution to my question not to use temp tables as the syntax is different. – Pascal Feb 16 '12 at 20:41
0

Oracle has temporary tables but you have to creat them at installation time:

  1. when installing your DB use CREATE TEMPORARY TABLE to create the table
  2. Each user can only see their own data, and this data is lost when then they end their session or commit their transaction so you can use for temporary processing.
Andrew Skirrow
  • 3,402
  • 18
  • 41