Link to home
Start Free TrialLog in
Avatar of mahjag
mahjag

asked on

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?
Avatar of Sean Stuber
Sean Stuber

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';
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
or you can try
select table_name from tab where table_name = 'blah'
select table_name from cat where table_name = 'blah'
CREATE TABLE IF EXISTS MyTable (..);
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.
this isn't legal syntax in Oracle

CREATE TABLE IF EXISTS MyTable (..);

Avatar of mahjag

ASKER

I get missing or invalid option when I run using if exists clause in oracle ORA-00922
create table if exists  is not legal syntax
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial