Re: synonym on synonym is it possible.

Michel Cadot <micadot_at_netcourrier.com> wrote in message news:7sb8mh$e5h$1_at_oceanite.cybercable.fr...
> You cannot create a public synonym on a public synonym
> but you can do it on a private synonym:

Not true.
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)

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.)
>?]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 Received on Thu Sep 23 1999 - 13:44:53 CDT