Question:

>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.)
>?]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
 

The possible is:
1. You do not have the privilige to access the table.
2. The synonym RANGER.SOGEN1 indicates to a nonexistent object.

for example, after the preceding script,

SQLWKS> connect demo
Connected.
SQLWKS> desc e2;
MGR-00360: object to be described does not exist

Answer 1:

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
 

Answer 2:

You can create a public synonym on a public 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;
 

Since, the u in here is for a object name in your schama, not the public's
schema.
You can verify the DBA_SYNONYMS, the table owner is you, not PUBLIC.

To create a public synonym on a public synonym,
you must use a trick: use "PUBLIC" to specify an object in public's schema.
See the following script:

SQLWKS> create public synonym e1 for scott.emp;
Statement processed.
SQLWKS> -- note the "PUBLIC" qualifier
SQLWKS> create public synonym e2 for "PUBLIC".e1;
Statement processed.
SQLWKS> desc e2;
Column Name                    Null?    Type
------------------------------ -------- ----
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   VARCHAR2(10)
JOB                                     VARCHAR2(9)
MGR                                     NUMBER(4)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2)
 

Answer 3:

You can create synonyms on synonyms as you please.  The only thing that should
be understood is that Oracle resolves a synonym at reference time, not at
creation time.  Back to the particular example, check if you have access to
objects referenced by RAGEN.SOGEN1.

Regards,
Karen Abgarian.

Answer 4:

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