Let us learn Synonym In Oracle

Niladri.Biswas
Posted by in Oracle category on for Beginner level | Points: 250 | Views : 5695 red flag

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

  1. Table
  2. Package
  3. View
  4. Materialized view
  5. Sequence
  6. Java Class
  7. Schema Object
  8. Stored Procedure
  9. User-defined object
  10. Function
  11. 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.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)