• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1581
  • Last Modified:

check table exists in oracle

is there a command in oracle to check if table already exists before creating a new table name - can this statement be in create like in create or replace objects (procedure or functions) is this simiar to creating or replace table if already exists?
0
mahjag
Asked:
mahjag
  • 6
  • 3
1 Solution
 
sdstuberCommented:
there is no create or replace for tables, you'll have to do that check yourself.

you can select for the table first though...


select * from dba_tables where owner = 'YOUR_OWNER' and table_name = 'YOUR_TABLE';

or, if it will be your own table....

select * from user_tables where table_name = 'YOUR_TABLE';
0
 
sdstuberCommented:
you could also try querying all_tables, but if you don't have access to the table you won't see it but you won't be able to create it either
0
 
karunamoorthyCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
karunamoorthyCommented:
or you can try
select table_name from tab where table_name = 'blah'
select table_name from cat where table_name = 'blah'
0
 
karunamoorthyCommented:
CREATE TABLE IF EXISTS MyTable (..);
0
 
sdstuberCommented:
definitely don't use the "select xxxx from your_table"  methods.

if you don't have access the error is "table or view does not exist"
so, you can get a false negative

to check for existence, you must use the data dictionary which is what the dba_tables and user_tables queries posted above (and inside the link) do.

the "desc"  keyword is a sql*plus command so it's only relevant inside that tool or other tools that support sql*plus commands (like Toad)  but all "describe" does is issue commands against the data dictionary for you.
0
 
sdstuberCommented:
this isn't legal syntax in Oracle

CREATE TABLE IF EXISTS MyTable (..);

0
 
mahjagAuthor Commented:
I get missing or invalid option when I run using if exists clause in oracle ORA-00922
0
 
sdstuberCommented:
create table if exists  is not legal syntax
0
 
sdstuberCommented:
you have to check before hand yourself using one of the data dictionary queries above
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now