Re: How to access Synonym when it has same name as that of a Table

basab.datta_at_gmail.com wrote:
> 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...