By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,212 Members | 2,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,212 IT Pros & Developers. It's quick & easy.

to check if a table exists

P: n/a
i promise no more silly questions after this

but how do i check if a table exists??
it is my understanding that the exists can only be used in the where
clause of the query.
i just want s'thing simple like
if exists ('test')
then ---good
else
--create it
end if;
thanx in advance!!!
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"DottingTheNet" <do***********@hotmail.com> wrote in message
news:11**************************@posting.google.c om...
i promise no more silly questions after this

but how do i check if a table exists??
it is my understanding that the exists can only be used in the where
clause of the query.
i just want s'thing simple like
if exists ('test')
then ---good
else
--create it
end if;
thanx in advance!!!


Check out the ALL_TABLES and USER_TABLES views in the "Database Reference"
manual.

Douglas Hawthorne
Jul 19 '05 #2

P: n/a
"Douglas Hawthorne" <do**************@yahoo.com.au> wrote in message news:<nv****************@news-server.bigpond.net.au>...
"DottingTheNet" <do***********@hotmail.com> wrote in message
news:11**************************@posting.google.c om...
i promise no more silly questions after this

but how do i check if a table exists??
it is my understanding that the exists can only be used in the where
clause of the query.
i just want s'thing simple like
if exists ('test')
then ---good
else
--create it
end if;
thanx in advance!!!


Check out the ALL_TABLES and USER_TABLES views in the "Database Reference"
manual.

Douglas Hawthorne


Something like
begin
select table_name into v_variable from all_tables
where owner = v_owner and table_name = v_table_name
-- found logic here
exception
when no_Data_found then ... execute immediate create logic
end;

You can probably find a working example/stored function in the archives.

HTH -- Mark D Powell --
Jul 19 '05 #3

P: n/a
do***********@hotmail.com (DottingTheNet) wrote in message news:<11**************************@posting.google. com>...
i promise no more silly questions after this

but how do i check if a table exists??
it is my understanding that the exists can only be used in the where
clause of the query.
i just want s'thing simple like
if exists ('test')
then ---good
else
--create it
end if;
thanx in advance!!!

When you have proper exception handling in your code, you don't NEED
to check whether a table exists. Stop programming silly tests like
this one, and LEARN pl/sql, start UNLEARNING your bad sqlserver habits
NOW

Oracle != Sqlserver.

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.