Sams Teach Yourself SQL in 21 Days, Fourth Edition

Creating Numerous Synonyms in a Single Bound

Another tedious and exhausting task is creating numerous synonyms, whether they are public or private. Only a DBA or a user who has the CREATE PUBLIC SYNONYM privilege can create public synonyms, but any user can create private synonyms.

The following example creates public synonyms for all tables owned by RYAN:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL PUB_SYN.SQL
SQL> SELECT 'CREATE PUBLIC SYNONYM ' || TABLE_NAME || ' FOR ' ||
  2          OWNER || '.' || TABLE_NAME || ';'
  3  FROM SYS.DBA_TABLES
  4  WHERE OWNER = 'RYAN'
  5  / CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY; CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC; CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS; CREATE ...