OraFAQ Forum: SQL & PL/SQL » How to check existence of Global Temp Tables

Home » SQL & PL/SQL » SQL & PL/SQL » How to check existence of Global Temp Tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to check existence of Global Temp Tables [message #17794]

Tue, 01 February 2005 01:53

Shah
Messages: 36
Registered: June 2002

Member

How can we check the existence of temp tables and then drop them? As we can do in SQL Server

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '##table_name')
DROP TABLE ##table_name

Report message to a moderator

Re: How to check existence of Global Temp Tables [message #17796 is a reply to message #17794]

Tue, 01 February 2005 02:07

Frank
Messages: 7901
Registered: March 2000

Senior Member

Just a word of advice: in Oracle, unlike SQL Server, you don't create and drop temporary tables on the fly. Create once, use multiple times and don't drop.

You can query sys.dba_tables /all_tables/user_tables. It should be there.

hth

Report message to a moderator

Re: How to check existence of Global Temp Tables [message #17797 is a reply to message #17796]

Tue, 01 February 2005 02:10

Shah
Messages: 36
Registered: June 2002

Member

But when I execute the procedure for the same session, I get an error saying object with the same name exists. How may I resolve this issue?

Report message to a moderator

Re: How to check existence of Global Temp Tables [message #17798 is a reply to message #17797]

Tue, 01 February 2005 02:36

Maaher
Messages: 7065
Registered: December 2001

Senior Member

As Frank stated, you just don't create and drop tables in Oracle. You create the temp table once, and use it whenever you need it. It's contents is erased whenever your transaction ends (i.e. on implicit/explicit commit/rollback) or whenever your session ends (depending on the definition) so why would you drop and recreate it?

The object itself isn't dropped...

From the Oracle SQL Reference(10g):
----------------------------------------------------------------------
Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.

A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords.

You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.
----------------------------------------------------------------------

MHE

Report message to a moderator

Re: How to check existence of Global Temp Tables [message #17799 is a reply to message #17798]

Tue, 01 February 2005 03:07

Shah
Messages: 36
Registered: June 2002

Member

But why I get error
ORA-00955 : name is already used by an existing object

when I execute the procedure second time

Report message to a moderator

Re: How to check existence of Global Temp Tables [message #17800 is a reply to message #17799]

Tue, 01 February 2005 03:31

Sudhakar Pulipaka
Messages: 19
Registered: April 2004

Junior Member

Hi,

I think you are creating the table in your proceudre.That's why you are getting error when you are executing the procdure second time.

Create temp table seperately and use that in your procedure.

Regards,
Sudhakar

Report message to a moderator

Re: How to check existence of Global Temp Tables [message #17802 is a reply to message #17799]

Tue, 01 February 2005 04:12

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Sudhakar Pulipaka is right, and that's what we are saying all along: the object itself (the temporary table) is permanent. It's the content that's temporary. You create the table once, and reuse as you please.

MHE

Report message to a moderator

Previous Topic:	SQL query
Next Topic:	partition and over statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 03 03:43:29 CDT 2025