Solved

Ms Sql temporary tables, to Oracle migration

Posted on 1998-04-28
1
649 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
1 Comment
 
LVL 3

Accepted Solution

by:
mgokman earned 120 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now