Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Apr 19, 2004

Automatically Create All Missing Synonyms in an Oracle Database

Synonyms are used in place of schema objects in professional database applications because the application user is typically different from the object owner schema.

The script below creates synonyms for all the relevant objects in your schema—if they dont exist automatically. This saves you from having to write hundreds of create synonym statements for the objects and from the possibility of forgetting statements which would then cause problems due to the missing synonyms.


declare
   cursor cur_objects is select object_name , owner from all_objects where 
object_type in('TABLE','VIEW','SEQUENCE') and owner in('&&SCHEMA_OWNER'); 
/* this script will prompt you for the schema owner*/

begin
for rec_objects in cur_objects loop
begin
dbms_output.put_line(rec_objects.object_name);
execute immediate('create public synonym ' || rec_objects.object_name || ' for ' 
|| rec_objects.owner ||'.'||rec_objects.object_name )


exception when others then
null;
end;
end loop;
end;
/
Shushil Srivastava
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap