visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: Oracle :: Question on Temporary Tables in Oracle
By: calvinsinger U.S.A.  Date: 12/09/2006 09:27:19  English  Points: 20 Status: Answered
Quality : Excellent
Hi,

In SQL Server stored procedures I can create or declare and fill up temporary tables and then use these tables, just like the permanent ones. That is I can join them with other permanent tables in a sql query. I find it to be a great feature.
For example one could do this
create table #output ( item varchar(64), item2 varchar(64) )
or
declare @output table (col1 int, col2 varchar(32))

And then just use the table variable @output or temporary table #output in SQL queries, joining them to permanent tables.

Question: Can we do something Equivalent in Oracle ?
By: VGR Date: 12/09/2006 19:35:17 English  Type : Answer
yes. In PL/SQL, you can use :

create global temporary table ...


but this depends on your Oracle version (let's assume you can do this)

The global temporary table will be created in the users temporary tablespace
By: VGR Date: 12/09/2006 19:37:25 English  Type : Comment
Please note that the lack of TEMPORARY tables is a known issue since Oracle version 8i ; for instance you can find a discussion to implement temporary tables in 8i as in postgreSql at this page


Usually, Oracle users just create normal tables and consider them "temporary" (while they're not)

best regards
By: VGR Date: 07/10/2006 08:37:56 English  Type : Comment
Hello,

if the above suggestions do answer your question, please chose one to close the question, else provide feedback. Thanks
By: OpConsole Date: 03/01/2007 18:46:13 English  Type : Comment
forced acceptance
By: noriegaa Date: 03/06/2007 23:11:49 English  Type : Comment
The global temporary table used with the CREATE GLOBAL TEMPORARY TABLE works normally and is dropped automatically when the instance is restarted. Rows can be preserve or delete upon commit. DMLs such inserts, updates and deletes work conventionally as regular tables.

I recommend this link for further input.

http://www.oracle.com/technology/products/rdb/htdocs/rdb7/implementing_procedure_result_sets.html

You could alternatively implement virtual tables using a user-defined datatype, and using PL-SQL tables of that data type. usually conversion between virtual tables and regular tables occur via the cast and table operators. Pipelining inserts from function returns is ideal for populating virtual tables with user-defined data types. There are packages provided for all necessary conversions.


Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 301.588060 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page