PL / SQL to create synonyms for all tables

[Joe Harry]

Guys,

I can't understand why this error should occur for the below PL / SQL block.

declare
 owner varchar2(2000);
 table_name varchar2(2000);
BEGIN
   FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='CERT_ADMIN') LOOP
      EXECUTE IMMEDIATE 'create public synonym '||R.table_name||' for '||owner||'.'||R.table_name;
   END LOOP;
END;

Below is what I get as error:

Error at line 1
ORA-00995: Fehlender oder ungültiger Synonymbezeichner // Invalid synonym identifier
ORA-06512: in Zeile 6

Script Terminated on line 1.

[Martin Vajsar]

Try this instead in SQL*plus:
set echo off set pagesize 0 set linesize 1000 spool create.sql select 'create public synonym ' || table_name || ' for ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner='CERT_ADMIN'; spol off This will create a script file (create.sql); when you run this script, you'll find out the statement which gave you this error.

I'd guess that the problem lies in table name which contains special character and needs to be quoted; you can modify your 6th line like this:
EXECUTE IMMEDIATE 'create public synonym "'||R.table_name||'" for '||owner||'."'||R.table_name||'"'; In any case, it is best to avoid creating public synonyms (they pollute the name space). Consider these possibilities instead:
1) ALTER SESSION SET CURRENT_SCHEMA command,
2) Create views instead of synonyms,
3) Use private instead of public synonyms.

[Joe Harry]

Thanks for the suggestions. One question though! Would a private Synonym be visible to other schemas? If yes, then what is private there?

[Martin Vajsar]

Joe Harry wrote:Thanks for the suggestions. One question though! Would a private Synonym be visible to other schemas? If yes, then what is private there?

No, it would not be visible to others. The idea is to create private synonyms in the schemas of all the users that need to use the tables. I don't like that very much though, I would always prefer views to private synonyms if the target is a view or a table.