--
-- sql/drop-all.sql
--
--
-- @author Andrew Piskorski (atp@piskorski.com)
-- @creation-date 2002/08/12
-- @cvs-id $Id: drop-all.sql,v 1.1.3.1 2002/12/19 21:59:28 andy Exp $
--

-- TODO Add some way of passing in the do_p variable, as if this
-- anonymous block was a procedure, so that we can default do_p to
-- false.  Don't want to make it a procedure, because then it would
-- hand when it tries to drop itself.
-- --atp@piskorski.com, 2002/08/12 21:41 EDT

declare
  user_name  user_users.username%TYPE;
  do_p       integer  := 0;
begin
  select username  into user_name
  from user_users ;

  if lower(user_name) like '%dev' or
     lower(user_name) like '%stag'
  then
    do_p := 1;
  else
    do_p := 0;
    dbms_output.put_line('WARNING: You may only run this script on Dev and Staging Oracle users!');
  end if;

  dbms_output.put_line('Current Oracle user is:  ' || user_name);
  if do_p = 1 then
    dbms_output.put_line('Dropping all objects owned by this user:');
  else 
    dbms_output.put_line('-- When do_p = 1, we will do the following:');
  end if;

  for v_obj in (
    select  object_name  ,object_type
      ,decode(object_type,
         'CLUSTER', ' including tables cascade constraints',
         'TABLE', ' cascade constraints', '')  as cascade_text
    from user_objects
    where object_type in (
      'CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM', 'FUNCTION',
      'PROCEDURE', 'PACKAGE' )
  ) loop

    if do_p = 1 then
      execute immediate
        'drop '  || v_obj.object_type  || ' '  || v_obj.object_name
        || v_obj.cascade_text
      ;
    else
      dbms_output.put_line(
        'drop '  || v_obj.object_type  || ' '  || v_obj.object_name
        || v_obj.cascade_text
      );
    end if;

  end loop;

end;
/
show errors