SQL Script

Dynamic Code Public Synonyms

Updated : 3-March-2002
Version : 2.0

Description

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.

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
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
	sql_stmt varchar2(1024);

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

FOR obj_rec in get_obj LOOP

   sql_stmt := 'create public synonym '||obj_rec.object_name||' for '||obj_rec.owner||'.'||obj_rec.object_name;
   dbms_output.put_line(sql_stmt);

   EXECUTE IMMEDIATE sql_stmt;

END LOOP;

END;
/

Previous Oracle Version Links

Version 8.0 - Dynamic Code Public Synonyms

Return to Index of SQL Scripts