Re: synonym on synonym is it possible.

You cannot create a public synonym on a public synonym but 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 <37E9049A.5FC0D81D_at_abbnm.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 Wed Sep 22 1999 - 13:54:28 CDT