I am using Oracle 9i and I have an issue when in following situation

CREATE TABLE BE_FRIEND (NAME VARCHAR(20), INSTITUTE VARCHAR(40))

CREATE TABLE LAME_DUCK (CLASSIFICATION CHAR(15), GENUS VARCHAR(20),
PHYLUM VARCHAR(40))

CREATE PUBLIC SYNONYM BE_FRIEND FOR LAME_DUCK

Now you can observe from above that 'BE_FRIEND' is a Table as well
as a PUBLIC Synonym. If I want to call the Synnonym Table how do I
call it?

When execute the SQL ' Select * from BE_FRIEND' it always returns the
data in the table. How to distinguish between a Table and a Synonym?

basab.datta (AT) gmail (DOT) com wrote:

Not - that is the purpose of a public synonym.

If you are referring to the object type (as in
"I want to know if be_friend ia a table, or a synonym
or both"), then query the all_objects view on object_name
and object_type.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

On Dec 24, 8:46*am, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Unfortunately I do not have a system in front of me to test with so I
can post a demo but try the following.

If you are a different user from the table owner then try select *
from owner.be_friend. Fully qualifing the table as owner.table_name
should avoid referencing the public synonym.

Generally speaking you should not be defining public synonyms with the
same name as an existing application object where the synonym
references an object with a different name than the synonym name.
That is, if the application table is prod.partsmaster then the public
synonym would be partsmaster. Sometimes you may want the name to be
different but most of the time it should be the same. Where different
objects need to be accessed using the same name you probably need to
use private rather than public synonyms.

If a user defines a private synonym partsmaster that points to
test.partsmaster then for that user his or her queries will find the
private synonym before looking for a public synonym. Also if this
user owns a table or view named partsmaster that table will be seen
before the public synonym.

HTH -- Mark D Powell --

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

basab.datta (AT) gmail (DOT) com wrote:
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...

On Dec 25, 3:59*pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Frank, thank you so much for your replies that indeed helps.