If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

How to use oracle temporary table?

I want to use temporary table,but when i search in oracle websites,i only find a example about using globle temporary table. I want to a normal tempory table not a globle temporary table.
And when i create a globle temporary table in a package function. A synex error occurs. Why? Could create statement not use in the package function? How to do,please tell me,thanks.thanks very much.

oh,no,I want...

the global temporary table is visible to all the sessions. I want to create temporary table in the package function and auto dropped when the session end.I want the temporary table created in a session and not visible to a another session.
In sql server, I can create local temporay table with # name and create global temporary table with ## name.
How to do in the oracle?

Re: oh,no,I want...

Originally posted by oceanju the global temporary table is visible to all the sessions. I want to create temporary table in the package function and auto dropped when the session end.I want the temporary table created in a session and not visible to a another session.
In sql server, I can create local temporay table with # name and create global temporary table with ## name.
How to do in the oracle?

Your #temp tables in sqlserver are equivalent to global temporary tables in oracle..The only difference being that you create the global temporary table once and for all.The data remains for either the duration of the session or for the duration of the time the user stays logged in.In the former case you create the table with on commit delete rows option and in the later case you use on commit preserve rows options..

so to sum up

sqlserver temp#=global temporary tables in oracle

create them once and not every time in a proceedure..

If you are migrating from sqlserver to oracle instead of #temp tables
being replaced by Global temporary tables consider using inline views..

It seems to me that the original question has not been answered, and 5 years later I now have the same question :( Any help appreciated.

I have a PL/SQL proc running in 10g.
In this procedure I execute a sequence of queries within a series of For Loops. The reult is the correct answer but terrible performance, and so it's been suggested that I try using GTTs (which I've never used before).

Now while I have found many pages describing the syntax of creating GTTs, I can nevertheless find nothing that explains how I might use them in my situation. From within the PL/SQL proc I would like to create a GTT using the 'AS subquery' syntax, thus providing a GTT with simplified data for me to access later within the same proc and called subprocs.

So at the start of my PL/SQL proc I tried to create the desired GTT, but I get a compile error complaining that it didn't expect the 'CREATE' keyword at that point.

a) So as in the original post, why can't I create a GTT from within a PL/SQL proc?
b) Ales said it's bad practice to try this. Why? At the moment it doesn't even seem possible, nevermind bad practice.
c) If this is not possile, then I don't understand how I am supposed to use temp tables to assist my performance issue? They are supposed to be used to store intermediate data. Isn't that what I am trying to do?

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.