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.
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.
Got it. Brilliant. Finally understood, implemented and working.
Thanks a lot.
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.