Home » SQL & PL/SQL » SQL & PL/SQL » Why does oracle allow to create such ambiguous synonyms??
Why does oracle allow to create such ambiguous synonyms?? [message #297176] Wed, 30 January 2008 08:10 Go to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Why does oracle allow to create such ambiguous synonyms??

See the following case:

SQL> conn user02/pass
SQL> create table tabl1 ( aa number );

Table created.

SQL> create table tabl2 ( aa number );

Table created.

SQL> insert into tabl1 values (1);

1 row created.

SQL> insert into tabl2 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> create public synonym tabl1 for user02.tabl2;

Synonym created.

SQL> create public synonym tabl2 for user02.tabl1;

Synonym created.


SQL> select * from tabl1; --here table name is used and not the synonym

AA
----------
1

SQL> select * from tabl2;

AA
----------
2

SQL> disc
SQL> conn anotheruser/pass
Connected.
SQL> select * from tabl1; --here synonym is used

AA
----------
2

SQL> select * from tabl2;

AA
----------
1
Re: Why does oracle allow to create such ambiguous synonyms?? [message #297177 is a reply to message #297176] Wed, 30 January 2008 08:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Because oracle has the (probably wrong in many cases) impression the developer should know what he is doing?

There might be valid business reasons, for example to have an "in" and "out" table in one schema, and reversely named synonyms in another schema where the application looks at the whole in/out direction from the other side.

One of the powers of Oracle is that you can tell it to do almost anything you want. Of course the developer has to know what he wants, and it is up to the developer to decide if it makes sense.
Re: Why does oracle allow to create such ambiguous synonyms?? [message #297182 is a reply to message #297176] Wed, 30 January 2008 08:40 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you created a PUBLIC synonym, the synonym is not an object in user02's schema!
If Oracle would go with your intuition, we would be out of object-names quite fast. As soon as somebody creates a public synonym, other users are not allowed to create a private(!) object with the same name!
Oracle first checks the current user's namespace and then the public synonyms.
Previous Topic: How to free exclusive row lock after deadlock???
Next Topic: After-update trigger
Goto Forum:
  


Current Time: Sun Dec 04 00:43:38 CST 2016

Total time taken to generate the page: 0.11473 seconds