Rem
Rem $Header: admin_drop_synonyms.sql 31-jul-2007.01:54:33 sktedla Exp $
Rem
Rem admin_drop_synonyms.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_drop_synonyms.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sktedla     07/09/07 - sktedla_rfi_backport_4726892_10.2.0.4
Rem    rpinnama    10/08/02 - Remove emd_mntr synonym also
Rem    rpinnama    10/04/02 - Drop the newly created public synonyms
Rem    skini       06/12/02 - Drop blackout synonym
Rem    rpinnama    06/03/02 - rpinnama_reorg_rep_scripts_3
Rem    rpinnama    05/20/02 - Created
Rem
set serveroutput on
declare 
  err number;
  c number;
begin

    -- Drop basic roles.
    begin
      execute immediate 'drop role MGMT_USER';
      exception 
         	when others then
          DBMS_OUTPUT.PUT_LINE('DropRep: Role MGMT_USER does not exist');
    end;

    --
    -- Drop the following synonyms related to REPOS Schema
    --

    begin
      for crec in (SELECT synonym_name,table_owner,table_name
                   FROM dba_synonyms 
                   WHERE owner = 'PUBLIC'
                   AND table_owner = '&EM_REPOS_USER')
      loop
        begin
          EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || crec.synonym_name;

          EXCEPTION 
	      when others then
              DBMS_OUTPUT.PUT_LINE('DropRep: Public synonym ' || crec.synonym_name || 
				   ' cannot be dropped');
              -- continue dropping other synonyms.
        end;
      end loop;
    end;

    begin
      for crec in (select role from sys.dba_roles where role like 'MGMT_%')
      loop
        execute immediate 'drop role ' || crec.role;
        commit;
      end loop;
      exception
        when NO_DATA_FOUND then
          DBMS_OUTPUT.PUT_LINE('DropRep: Roles like MGMT% do not exist');
        WHEN OTHERS THEN
          err := SQLCODE;
          DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE||' dropping roles');
    end;

    commit;
end;
/