SQL ScriptDynamic Public Synonyms |
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.
s_user - Create public synonyms for objects owner by this user
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 l_sql varchar2(254); cursor_id integer; result integer; 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 cursor_id:=dbms_sql.open_cursor; /* Tables first */ FOR tab_rec in get_tab LOOP l_sql := 'create public synonym '||tab_rec.table_name||' for '||tab_rec.owner||'.'||tab_rec.table_name; dbms_sql.parse(cursor_id,l_sql,1); result := dbms_sql.execute(cursor_id); END LOOP; /* Views */ FOR view_rec in get_view LOOP l_sql := 'create public synonym '||view_rec.view_name||' for '||view_rec.owner||'.'||view_rec.view_name; dbms_sql.parse(cursor_id,l_sql,1); result := dbms_sql.execute(cursor_id); END LOOP; /* Sequences */ FOR seq_rec in get_seq LOOP l_sql := 'create public synonym '||seq_rec.sequence_name||' for '||seq_rec.sequence_owner||'.'||seq_rec.sequence_name; dbms_sql.parse(cursor_id,l_sql,1); result := dbms_sql.execute(cursor_id); END LOOP; dbms_sql.close_cursor(cursor_id); END; /
Return to Index of SQL Scripts
Home
|
Company Profile
|
Services
|
Contact Us
|
SQL scripts and tips
|
Quiz
|