Remove the database objects from schema

----------------------------------------------------------------------------
-- Script to delete DB objects (XXPA)
----------------------------------------------------------------------------
declare
cursor c1 is
select object_type,
object_name
from all_objects
where owner='XXPA'
and object_name like 'XX/_%' escape '/'
order by object_type;

l_stmt varchar2(1000);
begin

for r1 in c1 loop
if r1.object_type='FUNCTION' THEN
l_stmt:= ' DROP FUNCTION '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SYNONYM' then
l_stmt:= ' DROP SYNONYM '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='PROCEDURE' then
l_stmt:= ' DROP PROCEDURE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='TABLE' then
l_stmt:= ' DROP TABLE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='VIEW' then
l_stmt:= ' DROP VIEW '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SEQUENCE' then
l_stmt:= ' DROP SEQUENCE '||r1.object_name;
execute immediate l_stmt;
else
l_stmt:= ' DROP PACKAGE '||r1.object_name;
execute immediate l_stmt;
end if;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;


----------------------------------------------------------------------------
-- Script to delete DB objects (APPS)
----------------------------------------------------------------------------


declare
cursor c1 is
select object_type,
object_name
from all_objects
where owner='APPS'
and object_name like 'XX/_PA/_%' escape '/'
order by object_type;

l_stmt varchar2(1000);
begin

for r1 in c1 loop
if r1.object_type='FUNCTION' THEN
l_stmt:= ' DROP FUNCTION '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SYNONYM' then
l_stmt:= ' DROP SYNONYM '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='PROCEDURE' then
l_stmt:= ' DROP PROCEDURE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='TABLE' then
l_stmt:= ' DROP TABLE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='VIEW' then
l_stmt:= ' DROP VIEW '||r1.object_name;
execute immediate l_stmt;
else
l_stmt:= ' DROP PACKAGE '||r1.object_name;
execute immediate l_stmt;
end if;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;


----------------------------------------------------------------------------
-- Revoke script
----------------------------------------------------------------------------
declare

cursor c1 is
select distinct table_name
from all_tab_privs
where grantor ='XXPA';

l_tab_name all_tab_privs.table_name%TYPE;
l_stmt varchar2(1000);
begin
for r1 in c1 loop
l_tab_name := r1.table_name;
l_stmt := 'REVOKE ALL ON '||l_tab_name||' FROM APPS ;';
execute immediate l_stmt;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;