I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS
construct.
Specifically, whenever I want to drop a table in MySQL, I do something like
DROP TABLE IF EXISTS `table_name`;
This way, if the table doesn't exist, the DROP
doesn't produce an error, and the script can continue.
Does Oracle have a similar mechanism? I realize I could use the following query to check if a table exists or not
SELECT * FROM dba_tables where table_name = 'table_name';
but the syntax for tying that together with a DROP
is escaping me.
Best Answer
The best and most efficient way is to catch the "table not found" exception: this avoids the overhead of checking if the table exists twice; and doesn't suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller:
ADDENDUM For reference, here are the equivalent blocks for other object types:
Sequence
View
Trigger
Index
Column
Database Link
Materialized View
Type
Constraint
Scheduler Job
User / Schema
Package
Procedure
Function
Tablespace
Synonym