SQL ScriptDynamic Code Public Synonyms |
This script creates public synonyms for all PACKAGES/PROCEDURES/FUNCTIONS 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 set serveroutput on size 1000000 accept s_user prompt 'Enter object OWNER to create synonyms FOR : ' prompt show user prompt 'Creating PUBLIC synonyms for PACKAGES,PROCEDURES,FUNCTION ONLY owned by &s_user' prompt DECLARE l_sql varchar2(254); cursor_id integer; result integer; cursor get_obj is select object_name,owner from all_objects where owner = UPPER('&s_user') and object_type IN ('PACKAGE','FUNCTION','PROCEDURE') and object_name not in (select synonym_name from all_synonyms where owner='PUBLIC'); BEGIN cursor_id:=dbms_sql.open_cursor; FOR obj_rec in get_obj LOOP l_sql := 'create public synonym '||obj_rec.object_name||' for '||obj_rec.owner||'.'||obj_rec.object_name; dbms_sql.parse(cursor_id,l_sql,1); dbms_output.put_line(l_sql); 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
|