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?
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
checkout the url
http://www.dba-oracle.com/bk_check_table_exists.htm
http://www.dba-oracle.com/bk_check_table_exists.htm
or you can try
select table_name from tab where table_name = 'blah'
select table_name from cat where table_name = 'blah'
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.
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 (..);
CREATE TABLE IF EXISTS MyTable (..);
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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';