Re: synonym on synonym is it possible.

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 23 Sep 1999 15:14:49 +0200
Message-ID: <7sd95n$5lo$1@oceanite.cybercable.fr>

You can create but not use a public synonym on a public synonym. On the other hand you can do it on a private synonym:

v734>create table t (col number);

Table created.

v734>create public synonym u for t;

Synonym created.

v734>desc u

Name                            Null?    Type


------------------------------- -------- ----


COL                                      NUMBER

v734>create public synonym v for u;

Synonym created.

v734>desc v
Object does not exist.

v734>select * from v;
select * from v

              *
ERROR at line 1:
ORA-00942: table or view does not exist

This is because Oracle search for an object u that you own but u is a public synonym and you can't use create public synonym v for public.u
because public is not a schema.
But that's not the same with private synonyms:

v734>drop public synonym v;

Synonym dropped.

v734>drop public synonym u;

Synonym dropped.

v734>create synonym u for t;

Synonym created.

v734>desc u

Name                            Null?    Type


------------------------------- -------- ----


COL                                      NUMBER

v734>create public synonym v for u;

Synonym created.

v734>desc v

Name                            Null?    Type


------------------------------- -------- ----


COL                                      NUMBER

v734>select * from v;

no rows selected

Tapan Trivedi a écrit dans le message
<6EC14873F507D3119B8C0090273C4349067A2F_at_jdasoftware.com>... Hey guys,
i have a problem with a user trying to create a synonym on a synonym. Is it possible to do this.

create public synonym dingdong for ranger.sogen1; ( SOGEN1 is itself a synonym on another table.) ›1]Synonym created.
SQL> select * from dba_synonyms where SYNONYM_NAME like 'DINGDONG';

OWNER                          SYNONYM_NAME
TABLE_OWNER                    TABLE_NAME                     DB_LINK


------------------------------ ------------------------------


------------------------------ ------------------------------


-------------------------


PUBLIC                         DINGDONG
RANGER                         SOGEN1

SQL> desc dingdong
Object does not exist.
Why does this happen ?

Any hints,experiences,suggestions will be appreciated.

Thanks a lot.

Tapan H Trivedi Received on Thu Sep 23 1999 - 08:14:49 CDT