aspose file tools*
The moose likes Oracle/OAS and the fly likes PL / SQL to create synonyms for all tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "PL / SQL to create synonyms for all tables" Watch "PL / SQL to create synonyms for all tables" New topic
Author

PL / SQL to create synonyms for all tables

Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9521
    
    2

Guys,

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



Below is what I get as error:



SCJP 1.4, SCWCD 1.4 - Hints for you, Certified Scrum Master
Did a rm -R / to find out that I lost my entire Linux installation!
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Try this instead in SQL*plus:
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:
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
Ranch Hand

Joined: Sep 26, 2006
Posts: 9521
    
    2

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
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: PL / SQL to create synonyms for all tables