Ms Sql temporary tables, to Oracle migration

Give some possible ways to migrate Ms sql temporary table's based logic inside stored procedures, to Oracle stored procedures.
LVL 3
mordiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mgokmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.