This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 9345
    
    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: 3606
    
  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: 9345
    
    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: 3606
    
  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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PL / SQL to create synonyms for all tables
 
Similar Threads
Handling cursor declared in oracle in java program
Can a oracle Stored procedure return a cursor as a return value?
What is wrong with this procedure?
wrong number or types of arguments in call to 'PUT_LINE'
Oracle PL / SQL Drop Table Exception Handling