Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PL / SQL to create synonyms for all tables

 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guys,

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



Below is what I get as error:


 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic