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.

Improve this question
2
  • 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. Commented Feb 15, 2012 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. Commented Feb 15, 2012 at 16:17

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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.
Actually your answer is a solution to my question not to use temp tables as the syntax is different.
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.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.