• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PL / SQL to create synonyms for all tables

 
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:


 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic