{"id":1998,"date":"2018-11-25T22:14:18","date_gmt":"2018-11-25T21:14:18","guid":{"rendered":"http:\/\/van-maanen.com\/?p=1998"},"modified":"2018-11-25T22:14:18","modified_gmt":"2018-11-25T21:14:18","slug":"drop-oracle-table","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=1998","title":{"rendered":"drop Oracle table"},"content":{"rendered":"<p>Oracle knows the command &#8220;drop table&#8221;. One may include this in a script where a table is first deleted (using &#8220;drop table&#8221;) and subsequently rebuilt with additional SQL statement.<br \/>\nBut what happens if the table doesn&#8217;t exist? One may then end up with an error  SQL Error: ORA-00942: &#8220;table or view does not exist&#8221;.<\/p>\n<p>How to avoid this error?<br \/>\nOracle doesn&#8217;t have a clause that reads as &#8220;if exists then drop table&#8221;. The solution to avoid this error is to set up a procedure that reads like:<\/p>\n<pre>\ncreate or replace PROCEDURE DROP_TABLE\n(\n  P_TABLE_NAME IN VARCHAR2\n) AS\nn_table           INT;\nstmt              VARCHAR(100);\nBEGIN\n  stmt := 'drop table ' || P_TABLE_NAME;\n  SELECT COUNT(*) INTO n_table FROM user_tables WHERE table_name = UPPER(p_table_name);\n  IF (n_table > 0) THEN\n    EXECUTE IMMEDIATE stmt;\n  END IF;\nEND DROP_TABLE;\n<\/pre>\n<p>This procedure can be called with &#8220;exec drop_table(&#8216;ff123&#8217;); If the table does not exist, we have no entries in user_tables. In that case, nothing happens. If we have a table that exists, the user_table has an entry and the &#8220;execute immediate stmt&#8221; is executed. This implies the table is dropped.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle knows the command &#8220;drop table&#8221;. One may include this in a script where a table is first deleted (using &#8220;drop table&#8221;) and subsequently rebuilt with additional SQL statement. But what happens if the table doesn&#8217;t exist? One may then end up with an error SQL Error: ORA-00942: &#8220;table or view does not exist&#8221;. How [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-1998","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1998","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1998"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1998\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1998"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}