Helper Oracle procedure for dropping objects.

delete_object.sql

create or replace procedure delete_object(object_name varchar2, object_type varchar2)
is
 v_counter number := 0;   
begin    
  if object_type = 'TABLE' then
    select count(*) into v_counter from user_tables where table_name = upper(object_name);
    if v_counter > 0 then          
      execute immediate 'drop table ' || object_name || ' cascade constraints'; 
      dbms_output.put_line('table ' || object_name || ' deleted');
    end if;   
  end if;
  if object_type = 'PROCEDURE' then
    select count(*) into v_counter from User_Objects where object_type = 'PROCEDURE' and OBJECT_NAME = upper(object_name);
      if v_counter > 0 then          
        execute immediate 'DROP PROCEDURE ' || object_name;   
      dbms_output.put_line('table ' || object_name || ' deleted');     
      end if; 
  end if;
  if object_type = 'FUNCTION' then
    select count(*) into v_counter from User_Objects where object_type = 'FUNCTION' and OBJECT_NAME = upper(object_name);
      if v_counter > 0 then          
        execute immediate 'DROP FUNCTION ' || object_name;   
        dbms_output.put_line('function ' || object_name || ' deleted');     
      end if; 
  end if;
  if object_type = 'TRIGGER' then
    select count(*) into v_counter from User_Triggers where TRIGGER_NAME = upper(object_name);
      if v_counter > 0 then          
        execute immediate 'DROP TRIGGER ' || object_name;
        dbms_output.put_line('trigger ' || object_name || ' deleted');
      end if; 
  end if;
  if object_type = 'VIEW' then
    select count(*) into v_counter from User_Views where VIEW_NAME = upper(object_name);
      if v_counter > 0 then          
        execute immediate 'DROP VIEW ' || object_name; 
        dbms_output.put_line('view ' || object_name || ' deleted');       
      end if; 
  end if;
  if object_type = 'SEQUENCE' then
    select count(*) into v_counter from user_sequences where sequence_name = upper(object_name);
      if v_counter > 0 then          
        execute immediate 'DROP SEQUENCE ' || object_name;       
        dbms_output.put_line('sequence ' || object_name || ' deleted'); 
      end if; 
  end if;
end;