Solved

Ms Sql temporary tables, to Oracle migration

Posted on 1998-04-28
1
682 Views
Last Modified: 2012-08-13
Give some possible ways to migrate Ms sql temporary table's based logic inside stored procedures, to Oracle stored procedures.
0
Comment
Question by:mordi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 3

Accepted Solution

by:
mgokman earned 120 total points
ID: 1080526
The only special thing about MS SQL Server temporary tables is that they are dropped automatically when you terminate your session. Usually temp tables are used to store some intermediate results and pass them to the next step in your procedure. The procedures  I've seen in Sybase and SQL Server used temp tables mostly to make sure they perform efficiently instead of relying on the optimizer's decisions.
First of all Oracle optimizer is much better than in SQL Server and you won't need to use temp tables so much in Oracle. OK, if this biased answer is not good enough, here are some options for you.
1. You can create and drop your own intermediate tables and use them the same way as the original temp tables. The table name must be unique so you could use various techniques to generate a unique table name and create/drop a table using DBMS_SQL package. I don't like this approach, because it will fragment your tablespace.
2.You could create all necessary tables once and keep them in your schema, but use truncate table or delete to get rid of its rows. If multiple concurrent sessions need to use these tables, you will need to come  up with some way to identify rows belonging to each session. In this case USERENV built-in function can be useful.

All above options have one significant drawback. If your session aborts, you may end up with the leftovers of those tables. From time to time you will have to clean up those tables.

3. Depending on the volume of data you need to keep in the temp tables, you can also use PL/SQL table types. Basically these are arrays in memory. You can manipulate their rows using subscripts. There are various functions associated with pl/sql tables. This approach will be the closest to temp tables, because all you memory structures will be released when your session is gone. The negative effect will be significant increase in memory needed for the shared pool (SGA).
4. The last and best approach is to forget about how you do it in SQL Server and rewrite your procs using Oracle features the way Oracle environment dictates. In general it is not a good idea to model one system into another one.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question