Oracle Drop Table If Exists

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!