SQL Script

Dynamic Public Synonyms

Updated : 5-Mar-2002
Version : 2.0

Description

This script creates public synonyms for all TABLES/VIEWS/SEQUENCES owned by a user. This script can be run by any user to create public synonyms for any other user as long as it has CREATE PUBLIC SYNONYM privilege.

Parameters

s_user - Create public synonyms for objects owner by this user

SQL Source

set verify off
set pause off
set doc off
set heading off

accept s_user prompt 'Enter object OWNER to create synonyms FOR : ' 
prompt
show user
prompt 'Creating PUBLIC synonyms for TABLES/VIEWS/SEQUENCES owned by &s_user'
prompt

DECLARE
	sql_stmt varchar2(1024);

cursor get_tab is
    select table_name,owner from all_tables 
    where owner = UPPER('&s_user')
    and table_name not in (select synonym_name
                           from all_synonyms
                           where owner='PUBLIC'); 

cursor get_view is
    select view_name,owner from all_views
    where owner=upper('&s_user')
    and view_name not in (select synonym_name
                           from all_synonyms
                           where owner='PUBLIC');

cursor get_seq is
    select sequence_name,sequence_owner from all_sequences
    where sequence_owner=upper('&s_user')
    and sequence_name not in (select synonym_name
                           from all_synonyms
                           where owner='PUBLIC');

BEGIN

/* Tables first */

FOR tab_rec in get_tab LOOP

   sql_stmt := 'create public synonym '||tab_rec.table_name||' for '||tab_rec.owner||'.'||tab_rec.table_name;

   EXECUTE IMMEDIATE sql_stmt;

END LOOP;

/* Views */

FOR view_rec in get_view LOOP

   sql_stmt := 'create public synonym '||view_rec.view_name||' for '||view_rec.owner||'.'||view_rec.view_name;

   EXECUTE IMMEDIATE sql_stmt;

END LOOP;

/* Sequences */

FOR seq_rec in get_seq LOOP

   sql_stmt := 'create public synonym '||seq_rec.sequence_name||' for '||seq_rec.sequence_owner||'.'||seq_rec.sequence_name;

   EXECUTE IMMEDIATE sql_stmt;

END LOOP;

END;
/

Previous Oracle Version Links

Version 8.0 - Dynamic Public Synonyms

Return to Index of SQL Scripts