sql server store procedure contains temp tables need to convert oracle?


I am struggling with migrating the temp tables (SQL server) to oracle. Mostly, oracle don't consider to use temporary table inside the store procedure but in sql server, they are using temp tables for  small fetching record and also manipulate same.
How to overcome  this issue.  I am also searching some online articles about migrating temp table to oracle but they are not clearly explained for my expectations.
i got information like using inline view, WITH clause, ref cursor instead of temp table. I am totally confused.
Please suggest me,  in which case may use Inline view, WITH clause, ref cursor.
This may be helpful for improve my knowledge and also doing job well.

As always thank you for your valuable time in helping out the newbies.
Alsatham hussain
alsatham hussainOracle developerAsked:
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.

PortletPaulEE Topic AdvisorCommented:
This broad question is IMPOSSIBLE to answer simply

Using WITH is probably the easiest to implement because in many cases you can simply mimic the creation of a temp table this way.

BUT having said that I often sense that T-SQL developers OVERUSE temp tables and quite often they are simply not needed in Oracle.
slightwv (䄆 Netminder) Commented:
I agree.  It all depends on what is in the old temp tables and how they are using them.

You shouldn't try to do a straight port from SQL Server to Oracle.  You'll likely end up with poorly performing code.

Oracle does have Global Temporary Tables (GTT).  You don't create them dynamically like you do in SQL Server.  You create them once and use them over and over again.  That might get you what you need.

If a GTT won't work for you and you need something more dynamic for a small amount of data from a really large table, you can use collections:

To repeat what is said above because it is a REALLY IMPORTANT statement:
quite often they are simply not needed in Oracle.

I would NOT use collections just because you can and the SQL Server code used TEMP tables.

I use GTTs and Collections in production but they are for very specific reasons.  Most of the time, I use cursors and grab the data exactly when I need it.

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
alsatham hussainOracle developerAuthor Commented:
sorry for the delay

 Thank you so much for your advice and guidance


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.