Temporary tables in Oracle


I'm trying to find the equivalent of temporary tables in Sybase in Oracle.

Is there any mechanism in Oracle that can replicate the speed and functionality of temporary tables in Sybase?

Appreciate any information!
Errang GenevreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

slightwv (䄆 Netminder) Commented:
not sure about Sybase but in Oracle you create what is called a Global Temporary Table.

These are created once and used forever.  Only the session that inserts data into them can see the data and the data is removed automatically on commit or when the session ends, depending on how you create it.


You do not create them at runtime like some databases do.

If you need this flexibility, you likely need to use in-memory tables or VARRAYs in pl/sql.

The more information you give us about what you want to do, the more accurate we can be with our answers.
PortletPaulEE Topic AdvisorCommented:
In my experience using temp tables isn't needed anywhere near as often as it is used in either Sybase or MSSQL. It's just another difference you need to grasp hold of.
slightwv (䄆 Netminder) Commented:
>>isn't needed anywhere near as often

I completely agree.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
:) >>"I completely agree."

Thanks! but it goes further than just the frequency of use. It's also a "way of thinking" difference IMHO

What I often see in TSQL "queries", particularly those using temp tables, is a sequence of events much more akin to a C program than a set-based SQL query.

So, my advice would be "change the way of thinking". Temp tables are NOT necessary most of the time.

I am not saying use of temp tables is universally bad, just not needed in Oracle anywhere near as much.
Errang GenevreAuthor Commented:
Well, my use case is fairly straightforward.

A stored procedure uses temporary tables to help with this task.

As for why I need to think of temporary tables, I'm trying to replicate the functionality of an old system; so I would need a way to compare the two.

I suppose the only way to go about it would be to implement the procedure in the "Oracle" way, and see if it matches up (hopefully beats) with the Sybase version.
PortletPaulEE Topic AdvisorCommented:
I do understand, but do also note you have not given any specifics on your particular problem. As slighwv states in the first comment, there are methods available using PL/SQL (not "just" SQL) that may assist.

nb: I would suggest looking at "explain plans" (Oracle's term for execution plans) as a primary performance tuning source.
slightwv (䄆 Netminder) Commented:
>>I suppose the only way to go about it would be to implement the procedure in the "Oracle" way, and see if it matches up (hopefully beats) with the Sybase version.

That is the only way.  Trying to replicate something to mirror something else then compare is a horrible way.

I could enter a Monster Truck into a drag race and try to "compare" but it likely wouldn't be a true comparison.

The only thing many RDBMSs have is the ANSI definition of RDBMS:  Create, Insert, Update, Delete and Select.  From there, everything goes out the window.

>>A stored procedure uses temporary tables to help with this task.

What is the task?  There are three ways I can solve this without knowing what it is:
-The Global Temporary Table (GTT) as suggested
-In-memory pl/sql table or VARRAY
-forget all about 'tables' and do it with native SQL

I prefer the last option.

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
Errang GenevreAuthor Commented:
Are there any tools out there that can migrate Sybase to Oracle? Just to get benchmark ratings?
slightwv (䄆 Netminder) Commented:
>>Are there any tools out there that can migrate Sybase to Oracle?

Take a look at:

>>Just to get benchmark ratings?

Sorry but I doubt you will get to compare apples to apples.  Oracle isn't a "run setup.exe and finish" database.  If you don't understand it, you'll not get accurate performance metrics.

Sure, you can probably get a database up and running but it likely won't be "well implemented" thus invalidating any performance comparisons.
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.