{"id":928,"date":"2015-07-26T09:56:41","date_gmt":"2015-07-26T09:56:41","guid":{"rendered":"http:\/\/www.van-maanen.com\/?p=928"},"modified":"2015-07-26T09:56:41","modified_gmt":"2015-07-26T09:56:41","slug":"dropping-a-table-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=928","title":{"rendered":"Dropping a table in Oracle"},"content":{"rendered":"<p>To drop a table is straightforward in Oracle. One might simply issue a drop table statement. Let us assume we have table HH. When &#8220;drop table HH&#8221; is fired, the table is removed. However, an error is returned if the stable doesn&#8217;t exist. Again: if table HH doesn&#8217;t exist and a SQL &#8220;drop table HH&#8221; is fired, an error is returned: .ORA-00942 is generated. In itsself, this is understable as a non-existent table is attempted to drop. Understably, such error is generated to warn us that something weird happened. In other DMBS, one might use use a statement like &#8220;drop if exists table HH&#8221;. However, Oracle doesn&#8217;t provide us with this facility.<br \/>\nIn Oracle, one needs to write a small PL SQL procedure to circumvent the error. The code reads like:<\/p>\n<pre>\ncreate or replace PROCEDURE DROP_TABLE(NAAM IN STRING) AS\nBEGIN\n   EXECUTE IMMEDIATE 'DROP TABLE '||NAAM;\nEXCEPTION\n   WHEN OTHERS THEN\n      IF SQLCODE != -942 THEN\n         RAISE;\n      END IF;\nEND DROP_TABLE;\n\/\n<\/pre>\n<p>This procedure can be called by &#8220;EXEC DROP_TABLE(&#8216;HH&#8217;);&#8221;.<br \/>\nThe procedure calls the statement DROP TABLE HH. Howver if HH doesn&#8217;t exist, an error is raised. Howver such error is handled as an exception that doesn&#8217;t lead to an error generated by the procedure.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>To drop a table is straightforward in Oracle. One might simply issue a drop table statement. Let us assume we have table HH. When &#8220;drop table HH&#8221; is fired, the table is removed. However, an error is returned if the stable doesn&#8217;t exist. Again: if table HH doesn&#8217;t exist and a SQL &#8220;drop table HH&#8221; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":933,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-928","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/928","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=928"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/928\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=928"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=928"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=928"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}