Procedure to create synonyms

This tip will allow you to create synonyms and grant privileges for a user or a user's group, on one object or all objects from a specific user. You can run this procedure from your system account without connecting to each user. It has been tested on Oracle v8.1.7.

--    N  O  T  E :    The user of these
--    utilities must have the following
--    system privs. EXPLICITLY granted
--
--
-- ANALYZE ANY
-- CREATE ANY SYNONYM
-- CREATE ANY PROCEDURE
-- DROP ANY PROCEDURE
-- EXECUTE ANY PROCEDURE
-- SELECT ANY TABLE
  create or replace procedure synonyms     -- Create synonyms for db objects
  (
     p_action  IN varchar2,                        -- C-Create or D-Drop
     p_owner   IN varchar2,                        -- Object's owner
     p_grantee IN varchar2,                        -- New synonyms's owner
     p_type    IN varchar2 default 'A',            -- T=table; V=view;
                                                   -- S=sequence; F=function;
                                                   -- P=procedure; K=package;
                                                   -- A=all objects
     p_object  IN varchar2 default '%'             -- specific object name
  )  is

     v_sql        varchar2(1000) := '';
     v_action     varchar2(10);
     v_role       char(1)        := 'N';
     v_exi_gran   number(6);

     e_exists_exception EXCEPTION;
     PRAGMA EXCEPTION_INIT(e_exists_exception, -955);    --  object name in
                                                         --  use exception
     e_not_exists_exception EXCEPTION;
     PRAGMA EXCEPTION_INIT(e_not_exists_exception, -1434);  -- object name
                                                            -- in use exception
     e_role_especific EXCEPTION;
     PRAGMA EXCEPTION_INIT(e_role_especific, -20335);    -- p_grantee is a role

  Begin
     begin
       select 'Y'               --  ask if p_grantee is a role
         into v_role
         from dba_roles
        where role = upper(p_grantee);
       begin
         for usr in (select grantee
                       from dba_role_privs
                      where granted_role = upper(p_grantee)
                        and grantee     != upper(p_owner)) loop
--  build the synonyms, acum role granted
            sinonimos(p_action,p_owner,usr.grantee,p_type,p_object); 
         end loop;
       end;
       raise e_role_especific;
     exception
       when no_data_found then
          v_role := 'N';
     end;

     if upper(p_action) = 'C' then
       v_action := 'create';
     elsif upper(p_action) = 'D' then
       v_action := 'drop';
     else
       v_action := 'create';
     end if;

--  obtain the objects which satisfy the parameter list
     for obj in(select *                                 
                from   dba_objects 
                where  owner = upper(p_owner) 
                and    object_name like upper(p_object) 
                and    object_type like decode(upper(p_type),'T','TABLE','V','VIEW','S','SEQUENCE','F','FUNCTION',
                                                             'P','PROCEDURE','K','PACKAGE','A','%')
                and    object_type in ('TABLE','VIEW','SEQUENCE','FUNCTION','PROCEDURE','PACKAGE')) loop

--  build dynamic synonym string
        v_sql := v_action||' synonym ' ||p_grantee||'.' ||obj.object_name; 

        if v_action = 'create' then

--  build dynamic synonym string
          v_sql := v_sql|| ' for '||p_owner|| '.'||obj.object_name;        
        end if;

        begin
           execute immediate v_sql;                     --  create the synonym
           dbms_output.enable(100000);
           dbms_output.put_line(v_sql);
        exception
           when e_exists_exception then         -- don't stop just ignore dups
              dbms_output.enable(100000);
              dbms_output.put_line('Synonym '||p_grantee||'.'||obj.object_name||' exists - skipping');
           when e_not_exists_exception then      -- don't stop just ignore dups
              dbms_output.enable(100000);
              dbms_output.put_line('Synonym '||p_grantee||'.'||obj.object_name||' not exists - skipping');
           when OTHERS then
              raise_application_error(-20322,'OTHERS raised for exec immediate: '||sqlerrm);
        end;

     end loop;

  Exception
     when e_role_especific then
        null; -- pass the exception up
     when OTHERS then
        raise_application_error(-20456,'OTHERS error occured in SYNONYMS: '||sqlerrm);

  End synonyms;
/

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.