Sometimes we want to clean up Oracle database, such as to drop a table if there exists, and do nothing if table does not exist; such as:
- drop table table if exists
Unfortunately, there is no such statement we can use, although this is supported by other RDBMS, such as MySQL, but not Oracle.
As a workaround, we can use catching the "table not found" exception:
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE yourtablename';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
The same solution can be used for other Oracle object types: sequence, function, etc. (just pay attention to the different SQLCODE value)
Reference:
1. http://stackoverflow.com/questions/1799128/oracle-if-table-exists
2. http://ora-exp.blogspot.jp/2013/03/oracle-drop-table-if-exists.html