Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Santhosh Reddy.

Asked: November 24, 2016 - 11:19 am UTC

Last updated: November 25, 2016 - 8:03 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

Please Go through the below code

SQL> select * from san; --querying tbale

NUM
----------
1
2

SQL> select * from sam; --querying table

VAR
----------
santhosh
reddy

SQL> SELECT * from t; --querying table

DAT
---------
22-MAR-96
01-JAN-01

SQL> CREATE PUBLIC SYNONYM san FOR SAM;

Synonym created.

SQL> CREATE PUBLIC SYNONYM saM FOR SAN;

Synonym created.

SQL> CREATE PUBLIC SYNONYM pt for t;

Synonym created.

SQL> select * from san;

NUM
----------
1
2

SQL> select * from sam;

VAR
----------
santhosh
reddy

SQL> SELECT * from pt; --SELECT Query With PUBLIC synonym queries t table

DAT
---------
22-MAR-96
01-JAN-01

SQL>

Before Creating Synonyms I queried tables i got results
Next I created synonyms sam,san and pt on tables san,sam,and t respectively.
after executing select * from pt
i got t table data because pt here synonym concept worked and given t data

But On san,sam tables why synonyms are not worked with select query
Because for pt synonym i got t data so why dont i get san data with sam synonym and sam data with san synonym?

and Connor said...

Name resolution is done from "inside" to "outside"

So when we do:

select * from XXX

we go through the following process

1) is "XXX" an object (including synonym) in my schema
2) if not, then is there a public synonym called XXX


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Santhosh Reddy Podduturi, November 25, 2016 - 5:12 am UTC

What is meant by inside to Outside?are these relates to any locations of namespace in oracle architecture?
Connor McDonald
November 25, 2016 - 8:03 am UTC

1) is "XXX" an object (including synonym) in my schema
2) if not, then is there a public synonym called XXX

Inside to outside name resolution

Subhrajit Banik, November 25, 2016 - 7:07 am UTC

In a SQL statement Oracle database attempts to qualify the object name referenced in the following order-
1)In the current schema the database searches for an object whose name matches with the referenced object name. If not then:
2)The database searches for a public synonym which matches with the referenced object name. If not then:
3)The database searches for a schema whose name matches with the referenced object name. If a schema is found then the database proceeds with Step-2(i.e. if the object name has two pieces like SCOTT.EMP where SCOTT will be treated as the first piece and EMP the second.)and after proceeding with Step-2 from Step-3 if no result is found the database returns an error.
If the Step-3 fails itself then also the database returns an error.

Now with respect to the above example in consideration to the 3 Steps, 'San' and 'Sam' are already found as objects in the current schema while for 'Pt' Step-2 is followed and hence the result.
Connor McDonald
November 25, 2016 - 8:03 am UTC

nice input