How to use oracle temporary table?

[oceanju]

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.

[ggnanaraj]

You can glean quite a lot from this article... check this out ...
http://dbasupport.com/oracle/ora8/page2.shtml

HTH.

[ales]

Oracle doesn't have "normal" temporary tables.
Global temporary tables should be created as other tables and persist in schema.
Creating and dropping GTT inside a package isn't very good practice.

[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?

[DaPi]

Originally posted by oceanju
I want the temporary table created in a session and not visible to a another session. The data is only visible to the session that created it - does it matter that other sessions know that the global temp table exists?

[hrishy]

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..

regards
Hrishy

[TomazZ]

Originally posted by hrishy
The data remains for either the duration of the session or for the duration of the time the user stays logged in.
The data remains for either the duration of the transaction or for the duration of the time the user stays logged in.

[Assaf]

Hi folks,

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?

Many thanks for your help
Assaf

[gandolf989]

Take a look at pl/sql collections.

[PAVB]

Well... actually the question got ansered five years ago.

Going to your follow up questions...

a) This is not best practice, that's why. You are supposed to create the GTT once and let it stay there.

b) Do you know what "best practices" are?

c) No, you do not understand. Yes, they are used to store "high volatility" data.

[DaPi]

Oracle does things a bit differently than you might expect - so you will run into trouble if you just copy methods from other databases:

- You create the GTT only once, before you write any PL/SQL (You don't have CREATE or DROP in the routine).
- Each session sees a view of the GTT which is independent of any other session. You just use it as if it had been created specially for you alone.

As Gandolf say, this may not be the best way.

[jhmartin]

1. Create the structure of the global temporary table once, outside of pl/sql.
2. Utilize that table in your procedures. The contents of the table will be local to your session and will automatically disappear when you either log out or commit, depending on how the table is configured.

Multiple sessions can use the same GTT at the same time, but they will not be able to see or interact with each others data. They will also not block each other for any action against that table.

[Assaf]

Got it. Brilliant. Finally understood, implemented and working.
Thanks a lot.

[tamilselvan]

Assaf,

One more thing I would like to add here:

The statistics on the GTT may not be optimal for all sessions when Oracle computes the execution plan.

What I did in one of the projects is:
1. Created a permanent table, loaded data and analyzed the table.
2. Verified that the execution plan is optimal.
3. Exported of the table and index stats from the permanent table
4. Imported the stats to the GTT.

After this exercise, I saw consistent execution plan.

You may need to do it if you see slow performance.