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

On 25 Jun 2004 10:47:08 -0700, neil@blueslate.net (Neil) wrote:

>I've researched via the internet and understand in a perfect
>environment a "real" table is the way to go

In a 'perfect' environment you don't need permanent temporary tables.
Fix your database design and/or your queries.


--
Sybrand Bakker, Senior Oracle DBA

Neil wrote:

> However, I want to drop the table after the cursor is
> returned.

Please explain WHY you want to DROP the table.  

If I understand Oracle's intent properly, GTTs are intended to have a
permanent definition, supplied by the DBA ideally ONCE in the life of the
database).  The term TEMP is relative to the data, not the definition.

Without understanding your requirements, to me it appears that your design
is fundementally in conflict with the intent and implementation of temp
tables.

/Hans

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 

a) the table is permanently defined (hence the term "temporary")
b) 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@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"

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

On 25 Jun 2004, neil@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