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