set echo off
--
--  Name: c_pubsynonyms.sql
--
--  Description: Synonyms - Create public synonyms for all objects in current schema that don't already have a public synonym
--
--  Usage: @c_pubsynonyms
--
set termout off
set verify off
set doc off
set feedback off
set recsep off
set pagesize 0
--
define lsize=200
set linesize &&lsize
--
define out_file=c_pubsynonyms.ddl
spool &&out_file
--
SELECT 'create public synonym '||rtrim(OBJECT_NAME)||' for '||user|| '.' ||rtrim(OBJECT_NAME)||';'
FROM user_objects
WHERE object_type IN ('TABLE','PACKAGE','PROCEDURE','FUNCTION','VIEW','SEQUENCE')
AND not exists (SELECT null
                FROM   dba_synonyms
                WHERE  owner = 'PUBLIC'
                  AND  dba_synonyms.synonym_name = user_objects.object_name
                  AND  dba_synonyms.table_owner  = user);
spool off
set doc off
set feedback on
set pagesize 30
set termout on
set echo on
start &&out_file
-- exit