April 29, 2009 at 12:05 am
I would like to know what replaces Temporary tables in T SQL Stored Procedures.If I have to write the same stored Procedure in Oracle. Taking into consideration same tables exist in both the database. I am aware of the date and time functions . Are cursors the only option????????.I am very much familiar with T SQL Stored Procs in Sybase as well as SQL Server 2005.
thank you
April 29, 2009 at 7:38 am
Nilesh (4/29/2009)
I would like to know what replaces Temporary tables in T SQL Stored Procedures.If I have to write the same stored Procedure in Oracle. Taking into consideration same tables exist in both the database. I am aware of the date and time functions . Are cursors the only option????????.I am very much familiar with T SQL Stored Procs in Sybase as well as SQL Server 2005.thank you
What makes you think that you have to replace temporary tables in T-SQL procedure?
Are cursors the only option? Definitely not, in fact cursors are the last option if you defintely can't find a set-based solution. Instead of temporary tables you could use variables or common table expressions(CTE), but that does not mean that you have to replace them.
[font="Verdana"]Markus Bohse[/font]
April 29, 2009 at 8:15 am
Its not like that .I mean If I have to write a Stored Procedure in Oracle that is already written in SQL Server .Taking into consideration that same tables exist in Oracle as well as SQl Server. What replaces Temporary tables that were in T-SQL Stored Procedures. I know that cursors are not a good choice.
What is the concept in Oracle that matches Temp tables , Table variables , CTE in SQL SErver.
thanks
April 29, 2009 at 8:38 am
Oh sorry, I thought you needed it the other way round.
Oracle knows the terms temporary tables, but they don't use the # prefix and are more like derived tables in T-SQL.
If you need a more permanent temp table you can use the CREATE GLOBAL TEMPORARY TABLE command.
This links might be helpfull
http://decipherinfosys.wordpress.com/2007/05/03/temporary-tables-oracle/
http://www.dba-oracle.com/t_temporary_tables_sql.htm
[font="Verdana"]Markus Bohse[/font]
April 29, 2009 at 10:46 am
Nilesh (4/29/2009)
I would like to know what replaces Temporary tables in T SQL Stored Procedures.If I have to write the same stored Procedure in Oracle. Taking into consideration same tables exist in both the database. I am aware of the date and time functions . Are cursors the only option????????.I am very much familiar with T SQL Stored Procs in Sybase as well as SQL Server 2005.thank you
The answer is Global Temporary Tables.
Here is how they work...
1) Create *once* your Global Temporary Table, this action is not going to actually create a table nor allocate any storage -it just adds an entry to a catalog. Be sure you include the option to drop table after completion.
2) Reference Global Temporary Table in your code as you do with any *normal* table; when you do that Oracle would actually create an instance of that Global Temporary Table for your process meaning, if several queries reference the GTT each one of them is going to have each own instance of the GTT.
3) When the query ends, space is deallocated.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply