Re: Global Temporary Taable

Neil 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.
>
> Thanks For your help

The term "global temporary table" is used because

1. the table is permanently defined (hence the term "temporary")
2. the data is local to your session (hence the term "local")

:-)

Seriously though, a couple of things

i) you don't need a temp table to pass a ref cursor around. ref cursor point to a resultset which does not need to "exist" as a temporary table

ii) if you are heading down the gtt route - you just populate it, use the data, and then forget about the data. It vanishes as soon as you exit the session.

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------