Solved

Ms Sql temporary tables, to Oracle migration

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot 2 43
how to replace '&' and '()' in sql query for oracle using regex 8 60
Problems using Provider=OraOLEDB.Oracle via VBScript/Classic ASP 5 44
SQL Query 34 80
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

914 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

15 Experts available now in Live!

Get 1:1 Help Now