Re: Global Temporary Taable

On 25 Jun 2004, neil_at_blueslate.net wrote:
> I am using a global temporary table with "on commit preserve
> rows" in order to pass a ref cursor back to the calling
> program. The data is returning fine. However, I want to drop
> the table after the cursor is returned. If I insert a drop
> table statment inside of the procedure I am getting the
> following error:
>
> ORA-14452: attempt to create, alter or drop an index on
> temporary table already in use. If I close the session and go
> through TOAD I can see the table but still can't drop it. I
> get the same error.
>
> I've researched via the internet and understand in a perfect
> environment a "real" table is the way to go. However,
> receiving strong pushback from the customer.

Just stop dropping the table. Leave it there. GTTs are used for session-based data but they aren't to be constantly dropped. They are tables, but their data is temporary.

If you have a connection pool, then you will need to clear it out every call.

-- 
Galen Boyer