In this article we will learn Synonym In Oracle
Introduction
Synonym is an alternative name/alias assigned to the objects like views, sequences, stored procedures etc.
Syntax
Create [or Replace] [Public] Synonym [Schema .] Synonym_Name
For [schema .] object_name [@ dblink];
It is an alias for the following types of objects
- Table
- Package
- View
- Materialized view
- Sequence
- Java Class
- Schema Object
- Stored Procedure
- User-defined object
- Function
- Synonym
Straight to Experiment
Create a Synonym
E.g. Let us create a synonym for the tblPlayers table
SQL> Create Synonym Players For tblPlayers;
Synonym Created
Then we can query the synonym as under
SQL> Select * From Players;
PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO DOB FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
11 P Chopra India 14-FEB-98
1 A Raman India 20-DEC-84 2000
2 B Kadir India 01-JAN-80 1000
3 C Nadir India 21-APR-00 3000
4 D Das India 11-NOV-80 500
5 E Punchkar India 14-FEB-80 12000
6 F Fateman USA 20-DEC-71 22000
7 G Gajani USA 01-JAN-90 999
8 H Hariharan USA 21-APR-99 7000
9 I Ikat USA 11-NOV-88 5999
10 J Jwar USA 14-FEB-98 7900
11 rows selected.
Insert record through the synonym
SQL> Insert into Players (PlayerID,PlayerFirstName,PlayerLastName,BelongsTo,DOB,FeePerMatch)
Values(12,'Z','Khan','India',To_Date('20/12/1984','DD/MM/YYYY'),2000);
1 row created.
SQL> Select * From Players;
PLAYERID PLAYERFIRS PLAYERLAST BELONGSTO DOB FEEPERMATCH
---------- ---------- ---------- ---------- --------- -----------
11 P Chopra India 14-FEB-98
12 Z Khan India 20-DEC-84 2000
1 A Raman India 20-DEC-84 2000
2 B Kadir India 01-JAN-80 1000
3 C Nadir India 21-APR-00 3000
4 D Das India 11-NOV-80 500
5 E Punchkar India 14-FEB-80 12000
6 F Fateman USA 20-DEC-71 22000
7 G Gajani USA 01-JAN-90 999
8 H Hariharan USA 21-APR-99 7000
9 I Ikat USA 11-NOV-88 5999
10 J Jwar USA 14-FEB-98 7900
12 rows selected.
In a similar way we can perform other DML operations
Drop a Synonym
SQL> Drop Synonym Players;
Synonym dropped
Conclusion
So in this article, we have seen how Oracle's Synonym works.Hope this will be helpful.Thanks for reading.