Solved

Ms Sql temporary tables, to Oracle migration

Posted on 1998-04-28
1
675 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

730 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