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

On Dec 25, 3:59 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> basab.da..._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...- Hide quoted text -
>
> - Show quoted text -

Frank, thank you so much for your replies that indeed helps. Received on Wed Dec 26 2007 - 07:42:47 CST