wildcard in creating synonymys

Question

I want to create a synonyms for the user in my database. These are my user and my schema owner

User : Scoot

Schema Owner : kitty

I would like to create a synonmys "Scoot" for accessing all the table under my schema owner "kitty". User "Scoot" have assigned the correct access right to the table. This is my script but do it accept any wildcard since I have plenty of the table and don't want to make my script too long

 CREATE SYNONYM Scoot.* FOR Kitty.*;

Answer 1

It's not possible, however you can easily generate the needed commands.

Use SQL*Plus and spool this to a file:

select 'create synonym scoot.'||table_name||' for kitty.'||table_name||';'
from all_tables
where owner='KITTY';

... then run it in SQL*Plus.