Re: How to access Synonym when it has same name as that of a Table
Date: Tue, 25 Dec 2007 11:59:52 +0100
> Thanks Mark & Frank.
> Actually I was trying to reproduce a client scenario.and wanted to
> refer to the Synonym (whatever it is refering to) rather than object
> created with "same name" either in the current schema or any other for
> which the current owner has privilages. What I get from your replies
> is the fact that it is NOT right to have Synonym created with a same
> name that of object which could very well exist in other schema also.
> From ALL_OBJECTS table, I can get on OBJECT_NAME and OBJECT_TYPE (as
> suggested by Frank) but exclusivly I cannot call a synonym. The
> scenario is very simple.
> SchemaX has a table called TEST and SchemaY also has TEST table. Now a
> PUBLIC Synonym was created with following syntax
> CREATE PUBLIC SYNONYM FOR SchemaX.TEST
> When SchemaY owner wants to call the PUBLIC Synonym TEST so that it
> can refer to SchemaX.TEST, how will he call it using the Synonym name?
> SchemaY owner can call TEST table either in his schema or use
> owner.table_name to refer to any other schema. Is there any way to
> ensure that I can make a call to PUBLIC Synonym (whatever it refers
> to) only rather that the object in my current schema having same name.
> Thank you so much
You cannot, to my knowledge. One way would be to call the tables prefixed by owner (e.g. schemaY.test vs test). You'll have to test this, as I do not have a test system available (XP forced a reinstall)
However, this is one of the reasons to be very careful with public synonyms - better to use private ones.
-- Regards, Frank van Bortel Top-posting is one way to shut me up...Received on Tue Dec 25 2007 - 04:59:52 CST