-- #############################################################################################
--
-- %Purpose: Set PRIVATE SYNONYMs to Schema of a connected Oracle User
--
-- Use:      Each Oracle User
--
-- #############################################################################################
--
set termout on;
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading off;
set tab on;
set scan on;
set verify off;
--
PROMPT
PROMPT **** PRIVATE SYNONYME auf waehlbares Schema setzen *****
PROMPT
PROMPT Sie brauchen Schreibrechte im aktuellen Verzeichnis,
PROMPT damit das SQL-Script gen_private_syn.sql generiert
PROMPT werden kann. Dieses wird (leider) nach der Ausfuehrung
PROMPT nicht automatisch geloescht, da sqlplus leider nicht
PROMPT weiss wie man das macht ...
PROMPT
PROMPT ********************************************************
PROMPT
WHENEVER SQLERROR EXIT SQL.CODE
ACCEPT schema_name CHAR PROMPT 'Enter Schema (Object Owner): '
--
-- Generate and run file with synonym commands in current directory
--
set termout off;
spool GEN_PRIVATE_SYN.SQL;
SELECT 'DROP SYNONYM '||synonym_name||';' FROM user_synonyms;
--
SELECT 'CREATE SYNONYM '||table_name||' FOR &&schema_name'||'.'||table_name ||';'
  FROM all_tables
 WHERE (UPPER(owner) LIKE UPPER('%&&schema_name%') OR
        UPPER(owner) LIKE UPPER('&&schema_name'));
--
SELECT 'CREATE SYNONYM '||sequence_name||' FOR &&schema_name'||'.'||sequence_name ||';'
  FROM all_sequences
 WHERE (UPPER(sequence_owner) LIKE UPPER('%&&schema_name%') OR
        UPPER(sequence_owner) LIKE UPPER('&&schema_name'));
--
SELECT DISTINCT 'CREATE SYNONYM '||name||' FOR &&schema_name'||'.'||name||';'
  FROM all_source
 WHERE (UPPER(owner) LIKE UPPER('%&&schema_name%') OR
        UPPER(owner) LIKE UPPER('&&schema_name'))
   AND UPPER(type) IN ('PROCEDURE','FUNCTION','PACKAGE');
spool off;
set feed on;
set termout on;
@GEN_PRIVATE_SYN.SQL;
EXIT;