When creating an update script for the database the DBA always nag me about objects to be dropped that don\u2019t exist. My view is that the end result is the same, you are dropping an objects to remove it, so in the end it\u2019s gone. If it\u2019s not there in the first place, then it\u2019s not there after the statement ran (gone as well).<\/p>\n
drop<\/span> <\/span>table<\/span> patrick purge\n \nORA<\/span>-<\/span>00942<\/span>: <\/span>table<\/span> <\/span>or<\/span> <\/span>view<\/span> does <\/span>not<\/span> exist<\/span><\/pre>\n<\/div>\n
The DBA doesn\u2019t want to see any ORA messages, so if you want your script to drop the object you first have to check for the existence of the object and if it does, then drop it. If it doesn\u2019t then there is nothing to do. (Oracle) MySQL has the possibility to drop if exists. That\u2019s why I created a utility that can be used to suppress any error message. Not, if there are dependencies to an object to be dropped, you will still get errors, but these are errors you want to see and which means \u2018back to the drawing board\u2019 for your script.<\/p>\n
\ncreate<\/span> <\/span>or<\/span> <\/span>replace<\/span> <\/span>procedure<\/span> drop_if_exists(object_name_in <\/span>in<\/span> <\/span>varchar2<\/span>,\n purge_in <\/span>in<\/span> boolean <\/span>default<\/span> true)\n authid <\/span>current_user<\/span> <\/span>as<\/span>\n <\/span>--<\/span> Procedure checks for the existance of an object and then drops it.<\/span>\n<\/span> <\/span>--<\/span> %param object_name_in The name of the object to be dropped<\/span>\n<\/span> <\/span>--<\/span> %param purge_in If the object to be dropped is a table, it can be purged from the Recycle bin<\/span>\n<\/span> <\/span>--<\/span> {*} True (Default)<\/span>\n<\/span> <\/span>--<\/span> {*} False<\/span>\n<\/span> <\/span>--<\/span> %author Patrick Barel<\/span>\n<\/span> <\/span>--<\/span> %version 1.0<\/span>\n<\/span>\n l_type user_objects.object_type<\/span>%<\/span>type;\n l_sql <\/span>varchar2<\/span>(<\/span>32767<\/span>);\n<\/span>begin<\/span>\n <\/span>begin<\/span>\n <\/span>select<\/span> uo.OBJECT_TYPE\n <\/span>into<\/span> l_type\n <\/span>from<\/span> user_objects uo\n <\/span>where<\/span> uo.<\/span>OBJECT_NAME<\/span> <\/span>=<\/span> <\/span>upper<\/span>(object_name_in);\n exception\n <\/span>when<\/span> others <\/span>then<\/span>\n dbms_output.put_line(<\/span>'<\/span>Nothing to do<\/span>'<\/span>);\n <\/span>end<\/span>;\n <\/span>if<\/span> l_type <\/span>is<\/span> <\/span>not<\/span> <\/span>null<\/span> <\/span>then<\/span>\n l_sql :<\/span>=<\/span> <\/span>'<\/span>DROP <\/span>'<\/span> <\/span>||<\/span> l_type <\/span>||<\/span> <\/span>'<\/span> <\/span>'<\/span> <\/span>||<\/span> object_name_in;\n <\/span>if<\/span> (l_type <\/span>=<\/span> <\/span>'<\/span>TABLE<\/span>'<\/span>) <\/span>and<\/span> purge_in <\/span>then<\/span>\n l_sql :<\/span>=<\/span> l_sql <\/span>||<\/span> <\/span>'<\/span> PURGE<\/span>'<\/span>;\n <\/span>end<\/span> <\/span>if<\/span>;\n <\/span>execute<\/span> immediate l_sql;\n dbms_output.put_line(object_name_in <\/span>||<\/span> <\/span>'<\/span> dropped<\/span>'<\/span>);\n <\/span>end<\/span> <\/span>if<\/span>;\n<\/span>end<\/span>;\n<\/span><\/pre>\n<\/div>\n
A way to call this procedure is like this:<\/p>\n
\n--<\/span> Drop table<\/span>\n--<\/span>drop table AMIS_LOG purge;<\/span>\n<\/span>exec<\/span> drop_if_exists(object_name_in <\/span>=><\/span> <\/span>'<\/span>AMIS_LOG<\/span>'<\/span>)\n<\/span>--<\/span> Create table<\/span>\n<\/span>create<\/span> <\/span>table<\/span> AMIS_LOG\n(\n log_id <\/span>number<\/span>,\n...<\/span><\/pre>\n<\/div>\n
If you have any suggestions for this script, let me know. This is a very simple version which doesn\u2019t do all the exception handling possible.<\/p>\n","protected":false},"excerpt":{"rendered":"
When creating an update script for the database the DBA always nag me about objects to be dropped that don\u2019t exist. My view is that the end result is the same, you are dropping an objects to remove it, so in the end it\u2019s gone. If it\u2019s not there in the first place, then it\u2019s […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-329","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/329","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=329"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/329\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=329"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}