rem
rem This script is used to drop all the stale objects prefixed by 'DBG$' in the 
rem runtime repository user schema. The objects end up being stale because of 
rem user abruptly exiting the OWB application while debugging a Map using Mapping Debugger.
rem
rem CAUTION:
rem This script should only be run by the user who has OWB sys admin priviliges and
rem has determined that all the objects that are prefixed by 'DBG$' in the 
rem runtime user schema are stale.
rem
rem Since the same Map can be debugged by bringing up multiple instances, running
rem this script by one user will cause disruptions for the other user running the same Map
rem using Mapping Debugger.
rem
rem USAGE:
rem   LOGON as user with OWB sys admin priviliges
rem
rem    @<OWB-HOME>/owb/bin/admin/cleanupalldebugobjects.sql
rem 
set serveroutput on;

 declare
 
    type obj_types is table of varchar2(30) index by binary_integer;
    type dbg_objects is table of varchar2(30) index by binary_integer;
    v_prefix varchar2(5) := 'DBG$';
 
    function initializetypes return obj_types as 
     v_types obj_types; 
     begin
       select 'PACKAGE', 'TABLE', 'VIEW', 'SYNONYM' into 
          v_types(1), v_types(2), v_types(3), v_types(4) from dual;
       return v_types;   
     end;
    
    function get_objects (objtype varchar2) return dbg_objects as 
     dbgobjs dbg_objects;
     v_object_name varchar2(30);
     i number := 1;
     cursor c1 is
       select object_name from USER_OBJECTS WHERE object_type = objtype AND substr(object_name, 1, length(v_prefix)) =  v_prefix;
     begin
       open c1;
       loop
         fetch c1 INTO v_object_name;
         exit when c1%NOTFOUND;
         -- dbms_output.put_line('Dropping object ' || v_object_name);
         dbgobjs(i) := v_object_name;
         i := i + 1;
       end loop;
       close c1;
       return dbgobjs;
     end;
    
    procedure drop_type (objtype varchar2) as
     v_objects dbg_objects;
     v_cmd varchar2(1000);
     begin
       v_objects := get_objects(objtype);
       if v_objects.count > 0 then
           for k in v_objects.first .. v_objects.last loop
              v_cmd := 'drop ' || objtype || ' ' || '"' || v_objects(k) || '"' ;
              dbms_output.put_line(v_cmd);
              execute immediate v_cmd;
           end loop;
       end if;    
     end;
     
    procedure cleanalldebugobjects as
      v_types obj_types;
      v_dbgobjs dbg_objects;
      v_syntx varchar2(1000);
      begin
        dbms_output.put_line('Finds and drops all the objects that are prefixed with ' || v_prefix);
        v_types := initializetypes;
        for i in v_types.first .. v_types.last loop
           -- dbms_output.put_line('Dropping object type ' || v_types(i));
           drop_type(v_types(i));
        end loop;
      end;
     
begin
  cleanalldebugobjects;
  commit;
end;
/