Re: Create synonym

Hong Zhou <hzhou_at_icarus.cc.uic.edu> wrote:
>
>I have 50 or so tables, now I want to create synonym for them,
>same name as table name. How can I do this without manully one by one?
>

This scripts assumes all the tables for which public synonyms are to be created are owned by one user.

set pagesize 0;
set heading off;
set feedback off;
set verify off;
column dummy noprint;

spool syns.sql
(select 2 dummy,'create public synonym '||object_name||

       ' for '||owner||'.'||object_name||';'   from all_objects
where object_type in ('TABLE','PROCEDURE','PACKAGE','FUNCTION','VIEW')   and owner = upper('&1')
union
select 1 dummy,'drop public synonym '||object_name||';' from all_objects
where object_type in ('TABLE','PROCEDURE','PACKAGE','FUNCTION','VIEW')   and owner = upper('&1'))
order by 1;

spool off;

Edit file syns.sql to remove unwanted synonym creations.

start syns.sql

Hope this helps.

Sanjay D.S.
Oracle Consultant
Prudential Securities, Inc. Received on Wed May 01 1996 - 00:00:00 CEST